Restoring an SQLServer 2000 database - questions...
-
I needed to restore a rather large database (mdf is 307MB - ouch).
going on 3 hours now since restore was started, and here are the log entries so far...
2004-03-30 10:52:44.60 spid3 Recovery complete.
2004-03-30 10:52:44.60 spid3 SQL global counter collection task is created.
2004-03-30 10:56:41.06 spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-03-30 10:59:01.17 spid51 Starting up database 'emweb'.
2004-03-30 10:59:01.18 spid51 Bypassing recovery for database 'emweb' because it is marked IN LOAD.
2004-03-30 10:59:01.27 backup Database restored: Database: emweb, creation date(time): 2004/03/17(17:57:54), first LSN: 1450:42:1, last LSN: 1450:45:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\EMSQL_BKPS\emweb.BAK'}).
2004-03-30 10:59:02.50 spid51 Starting up database 'emweb'.
2004-03-30 10:59:02.50 spid51 Bypassing recovery for database 'emweb' because it is marked IN LOAD.
2004-03-30 10:59:02.54 backup Database changes restored: Database: emweb, creation date(time): 2004/03/29(13:38:48), first LSN: 1450:91:1, last LSN: 1450:93:1, number of dump devices: 1, device information: (FILE=4, TYPE=DISK: {'E:\EMSQL_DIFFS\emweb.BAK'}).
2004-03-30 10:59:02.86 spid51 Starting up database 'emweb'.
2004-03-30 10:59:02.86 spid51 Bypassing recovery for database 'emweb' because it is marked IN LOAD.
2004-03-30 10:59:02.88 backup Log restored: Database: emweb, creation date(time): 2004/03/29(13:38:48), first LSN: 1450:91:1, last LSN: 1450:93:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\EMSQLT_BKPS\emweb_LOG.trn'}).
2004-03-30 12:55:43.32 spid63 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
my questions are:
- how do I know when I am done?
- is there anyway to see a progress of the restore (EM just says the database is loading)
- do I need to do anything after restore is done (like restart the server)
Thanks
-
First, that's NOT a large database. I've restored a 200+ GB database (yes, that's GIGABYTE).
Next, how did you do the restore? Can you post your script?
It looks like you may have used WITH NORECOVERY which will leave the database in LOADING state awaiting other backup files.
WITH NORECOVERY is used when you have to also restore a differetial or one or more transaction logs.
If you used WITH NORECOVERY, you could just run:
RESTORE DATABASE emweb WITH RECOVERY
-SQLBill
-
I agree with Bill. It sounds like you used the RECOVERY switch and need to run the script Bill gave to get you out of the "Loading" state.
I'm sure glad most of by databases are only 10-20 Gigs! We do have one I help watch that is 800 gigs. I dread the day we have to restore that one!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Gary,
Just to let you know, approximately 200 GB took me just over 6 hours to restore.
Win2K Advanced Server, 4 Processor, SQL Server 2000.
-SQLBill
-
THANKS much !!! (Yes, it isn't that large a database when you think about, compared to the others we have
on this system it is one of the largest).
Obviously I need to do a couple of practice restores on dummy databases to get this down.
My big concern was after 3 hours, and having no gauge as to how long it should take, trying to figure out how long restores really take on SQL Server. I know it depends on lots of variables, but my only previous experience is with Teradata, and those databases are HUGE, but it you have enough AMPs processing large DBs on those systems isn't a big deal.
Regards
randyv
Certified Teradata Master
and terrified SQL Server user
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply