How long for a restore?

[SQLBill]

I am using SQL Server 2000 on a Win2K server with 4 processors. Restoring a database with 192+ GB of data.

I ran the command:

RESTORE DATABASE mydatabase
FROM mydbfull
WITH NORECOVERY
GO
RESTORE DATABASE mydatabase
FROM mydbdiff
WITH NORECOVERY
GO
RESTORE LOG mydatabase
FROM mydbtranslog
WITH RECOVERY
GO

1. How long should this restore take?
2. How can I find how far along it is?

-SQLBill

 

[SQLBill]

It appears my database is restored (that's what the logs say), but Enterprise Manager shows it as (LOADING). What does that mean (I can't find it in BOL)? My differential and transaction log restores failed because the database is in that state.

-SQLBill

 

[MeanGreen]

LOADING means you are still recovering the database because you have the "WITH NORECOVERY" on your restore.
What is wrong with your DIfferential or log files? If your differential and log files are corrupt, you will have to run the restore again but "WITH RECOVERY" on the database. Of course, this means you have lost data too.

Good Luck.

 

[SQLBill]

Would a 192+GB take a while to 'load' before the differential can be applied?

-SQLBill

 

[MeanGreen]

I would think that a 192+GB database may take a while, but not sure how long. With the changes that came with SQL 7.0, the restores became much quicker. Knowing you have quad processors and plenty of horsepower, I am not sure how long it might take. I would think that if you ran the restore from your query analyzer, that when the cursor returned, then it was ready for your differential file. Try the differential or log file again and see what the error is.

Good Luck.

 

[SQLBill]

The error is:
Cannot apply the backup on device 'mydbdiff' to database 'mydatabase'

-SQLBill

 

[SQLBill]

Is there a way to 'break' the WITH NORECOVERY? In other words, can I send a command that tells the database to go ahead and RECOVER?

-SQLBill

 

[MeanGreen]

I don't think so. I think you have to start over with the restore (BUMMER) and use the with RECOVERY.

GOOD LUCK!

 

[SQLBill]

Thanks, that's what I'm thinking also. So how do I stop the current process (it's waiting for more restore data) so I can start over? Do I just stop the services?

-SQLBill

 

[MeanGreen]

I think you can just run your first restore "WITH RECOVERY" and it will overwrite the database. It has been about a year since I had to recover a large database.

Good Luck.