Sql-server – recover a SQL database from Standby/Read-Only without a standby file

recoverysql serversql-server-2008standby

Is there any way to recover a SQL database from Standby/Read-Only mode if the "standby file" is missing?

(I see some references calling this a TUF file in a log-shipping scenario, but in my case, it was just a manual restore and the standby file had a BAK extension.)

Doing a "RESTORE DATABASE foo WITH RECOVERY" results in a fatal error:

During startup of warm standby database 'foo', its standby file
('path\filename.bak') was inaccessible to the RESTORE statement.
The operating system error was '5(Access is denied.)'. Diagnose the
operating system error, correct the problem, and retry startup.

The restore was done a while ago, and unfortunately the standby file was in the backup folder and got removed by our folder cleanup routine. No copy of the file exists on any other media. The original backups that were used to restore to this point are also long gone.

Fortunately this isn't a big deal for us (it was a temp restore), but I couldn't find any other useful references out there on this question, other than "don't remove that file".

If it came down to it, I suppose I could script out the entire database, or use SSIS to copy all objects to a new database container, since I have a read-only copy of the db in front of me.

Any way to fix this issue? I certainly don't expect that I'd be able to flip it back to "restoring" and continue doing subsequent restores or anything, I'd just like to force it into normal online mode in its current state, so that I can do things like change permissions, or change the recovery mode, or take a new full backup or something.

Best Answer

Turns out it can be done if you use CONTINUE_AFTER_ERROR

RESTORE DATABASE foo WITH RECOVERY, CONTINUE_AFTER_ERROR

I did still get a warning when I tried it but then did a CHECKDB and received no errors.

RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE successfully processed 0 pages in 4.180 seconds (0.000 MB/sec).
Msg 3441, Level 17, State 1, Line 13
During startup of warm standby database 'foo' (database ID 46), its standby file ('C:\MSSQL\Backup\foo_standby') was inaccessible to the RESTORE statement. The operating system error was '2(The system cannot find the file specified.)'. Diagnose the operating system error, correct the problem, and retry startup.

Given the warning I'm not 100% certain if I would try it on a production database where I didn't have a good backup. Given that it's a temp restore it might be worth giving it a shot.

Related Question