Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I tried to restore my database from a snapshot. This usually took around a minute to complete the last couple of times. When I did it today, it didn't complete for around 30 minutes and the spid was in a suspended state. I stopped the query and now my database is stuck in restoring state and my snapshot is unavailable. Am I screwed?

USE master;
RESTORE DATABASE QA from 
DATABASE_SNAPSHOT = 'QA_Snap_Testing';
GO
share
    
Well does this work if I'm restoring from a snapshot instead of a backup? –  Aravindh Apr 3 '13 at 15:00
1  
Do you have any messages in the SQL Error log? The log should tell you what phase the the recovery is in. –  Mike Fal Apr 3 '13 at 15:36
comments disabled on deleted / locked posts

migration rejected from stackoverflow.com Sep 2 '13 at 5:28

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Mark Storey-Smith, RolandoMySQLDBA, Max Vernon, Mat, dezso Sep 2 '13 at 5:28

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers

This has happened to me. I would NOT recommend this unless you really can afford to lose the db and restore from an earlier backup. Our solution was to restart the services. Everything popped back up, but it was as if a restore were never attempted. However, it is a very low priority server and we can go back to any backup, as it is used as a staging server and the actual data in it isn't that important.

This has actually happened to this server several times, i suspect it has to do with the fact that we're restoring over a slow network.

One thing to check is if any other spids are connected to either the database or the snapshot.

share
add comment

You killed the restore, so your database is in recovery. Look here for estimating how long it will take to recover: http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/

When restoring or backing up dbs, this article has a handy script which will estimate when the backup or restore will finish: http://www.eraofdata.com/working-out-how-long-a-sql-server-backuprestore-will-take/

Also, when things appear to be "stuck", look for red flags like blocking and research wait types. There are several variations of sp_who3 available on the web to reveal potential issues. Here's a few from Denny Cherry: http://mrdenny.com/applications/sp_who3

share
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.