SQL Server 2005 Stuck in Restore mode

[bikerboy718]

This morning I walked into a problem with one of our reporting servers. Log shipping to it failed one of the databases on the server and now the database retoring to is stuck in restore mode. Can anyone tell me how I can get the database out of restore mode?

Any help is greatly appreciated.



With Great Power Comes Great Responsibility!!!

Michael

 

[ptheriault]

There is a status field in the sysdatabases tables.
Here are the valid options.

Status bits, some of which can be set by the user with sp_dboption (read only, dbo use only, single user, and so on):
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with
sp_dboption.
8 = trunc. log on chkpt; set with
sp_dboption.
16 = torn page detection, set with
sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with
sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.

You can update the status value, restart the instance. Most likely the database will come up suspect but you be able to start a restore.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bikerboy718]

Thanks for the post. I did find away to remove the database from recovery but I think this way is more efficient.

With Great Power Comes Great Responsibility!!!

Michael