Restore hangs for 5 hours? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Restore hangs for 5 hours?

Author

Topic

janetb
Yak Posting Veteran

71 Posts

Posted - 2007-11-08 : 14:28:08

Had a problem in a database and wanted to restore from disk as of 11/5/2007 (full backup). This particular database is only really updated about once a month, and changes were made on the sixth, so I thought this would be safe.

So (be kind - not an SQL guru), I ran something similar to:
RESTORE DATABASE myDatabase
FROM DISK = '\\serverName\sqlbackups\myDatabase\myDatabase_backup_200711052320.bak'
WITH NORECOVERY, STOPAT = 'Nov 05, 2007 11:21 PM'

myDatabase_backup_200711052320.bak is 42,084kb and has been running since 9:30 this morning (it's 2:20ish now). I've tried to take offline, stop/start sql services, restart server. Nothing works. It just keeps reading myDatabase (restoring).

Anybody help me out? How do I get it to stop and then do a clean restore?

Thanks so much,
Janet

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-08 : 14:44:34

You can't use STOPAT with RESTORE DATABASE. STOPAT is for the restoration of transaction logs (RESTORE LOG).

You should also run your restore statements with the STATS option so that you know if it is performing work.

Your database is no longer restoring since you did stop/start. It is just showing restoring as the database status. You'll need to rerun the correct commands against it to get it into the correct status.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

janetb
Yak Posting Veteran

71 Posts

Posted - 2007-11-08 : 15:30:04

Tara,

Obviously, I've screwed up. Can you help me out with how to fix it? Kinda the reason for the post? Please?

Janet

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-08 : 15:32:04

Run this if no log to apply:

RESTORE DATABASE myDatabase
FROM DISK = '\\serverName\sqlbackups\myDatabase\myDatabase_backup_200711052320.bak'
WITH RECOVERY

Kristen
Test

22859 Posts

Posted - 2007-11-08 : 15:37:44

WITH RECOVERY, STATS = 10

perhaps? (That will show progress each "10%" - so you know its still working. There may be a delay before it starts and shows anything at all - that's normal!)

janetb
Yak Posting Veteran

71 Posts

Posted - 2007-11-08 : 15:40:55

Tara,
Thanks. Well, it scared the beejeezes out of me, but I just tried doing another restore, and it worked fine??? Nevermind, and thanks for replying.... Would've thought that to compound the error, but instead it fixed it. Appreciate your time.
Janet

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-08 : 15:42:40

Next time specify WITH RECOVERY instead of WITH NORECOVERY if you don't have additional files to apply.

Please note that your STOPAT did not work for your command, so it restored the entire full backup.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources