SQL Server Performance Forum – Threads Archive
Backup Restore Stuck in "Restoring…" State
Hi, I’m trying to restore a backup on a sql server 2005 instance. I needed to restore this using the WITH NORECOVERY option (I’m in the process of configuring database mirroring). When the restore completes, the database is stuck in a "Restoring…." state on SQL Server Management studio. I try to click into its properties and get an error message, saying it’s still in the middle of a restore. The database is very small (about 26 MB) and the log file size is about 1Gb. I’ve repeated the restore several times and this happens each time. Any ideas? ThanksHi,
here is a excerpt from BOL for *NORECOVERY* option
quote:NORECOVERY
Instructs the restore operation to not roll back any uncommitted transactions. Either the NORECOVERY or STANDBY option must be specified if another transaction log has to be applied. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.
SQL Server requires that the WITH NORECOVERY option be used on all but the final RESTORE statement when restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed (for example, a full database backup followed by a differential database backup).
Note When specifying the NORECOVERY option, the database is not usable in this intermediate, nonrecovered state.
When used with a file or filegroup restore operation, NORECOVERY forces the database to remain in the restoring state after the restore operation. This is useful in either of these situations: A restore script is being run and the log is always being applied.
A sequence of file restores is used and the database is not intended to be usable between two of the restore operations.
Try to run restore yourdb with recovery
Regards
Hemantgiri S. GoswamiWhen used with a file or filegroup restore operation, NORECOVERY forces the database to remain in the restoring state after the restore operation. This is useful in either of these situations: A restore script is being run and the log is always being applied.
A sequence of file restores is used and the database is not intended to be usable between two of the restore operations.
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami
————————
http://hemantgirisgoswami.blogspot.com
As explained if you have restored the datbase WITH NORECOVERY clause then it will be inaccessible for any process…follow as suggested to get in online. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Thanks for the information. I’ve been reading through database mirroring configuration guides and they all specify to restore your database on the secondary server using the WITH NORECOVERY option. Why is this so?
Yes, inorder to make the mirror database not availble for usage and it is by default.
I don’t feel changing this option and for disaster recovery purpose this is good. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
]]>