Database Hung in Restoring State

Question

Database Hung in Restoring State

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 21, 2012 at 6:28 am

#254037

I was restoring two Databases and the Server Locked up and now they are hung in a Restoring State.
I had just created them and I need to restore from a backup from a different server.
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the exact command you ran to restore them?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

What she means by this is did you run restore WITH RECOVERY.

If you didn't you can try to do so now (assuming you don't need to restore tlogs as well).

RESTORE DATABASE YourDB WITH RECOVERY

Nothing more, nothing less.

If the restore crashed somehow you can always restart from scratch (WITH REPLACE).

Edit 2 : Just checked in bol and WITH RECOVERY is the default option if nothing is specified.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

This is the statement that I ran,

RESTORE DATABASE DataArchive

FROM DISK = 'I:DataArchive_backup_201201201349.bak'

WITH REPLACE,

MOVE 'DataArchive' TO 'D:\SQLServer\Data\DataArchive.mdf',

MOVE 'DataArchive_log' TO 'D:\SQLServer\Log\DataArchive.ldf'

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I tried taking the Databases offline.

I got an error but I was able to restore both databases.

I have moved 17 of 18 Databases.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Messages in the error log?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

GilaMonster (1/21/2012)
Messages in the error log?

Nothing relative to this issue.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

So what is the recommended way of getting out of this situation?

Somehow I have the last Database to be restored in a restoring state.

I can't restore it until the compression of the 170GB backup file is finished and the file is copied over the network and uncompressed.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

RESTORE DATABASE <db name> WITH RECOVERY

If that doesn't work, the restore did not complete and you'll need to re-do it, either from scratch or WITH RESTART

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

carl.stout Valued Member Points: 69 More actions · Answer 9

Try RESTORE DATABASE <DBName> WITH RECOVERY;

If that does not work then drop the database and start the restore process again.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Thanks Gail. It worked.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/