undo recovery

[maswien]

The syntax for restore command has an standby option, the a file name is required when undo is needed, I can't find anywhere in the BOL about how to undo the recovery, anyone used that before? Thanks.

 

[mrdenny]

Do you mean to put the database back into read/write mode?

You can either re-restore the last transaction log leaving off the STANDBY flag or RESTORE DATABASE DatabaseName with RECOVERY.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[maswien]

denny, I meant the standby file need to be specified and I don't know what this file is ued for.

 

[mrdenny]

Here's what BOL says.

Specifies the undo file name so the recovery effects can be undone. The size required for the undo file depends on the volume of undo actions resulting from uncommitted transactions. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.

STANDBY allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores.

Here is some more useful info that I found in BOL.

When a database or transaction log is restored in standby mode, recovery needs to roll back any uncommitted transactions so that the database can be left in a logically consistent state and used, if necessary, for read-only purposes. Pages in the database affected by the uncommitted, rolled back transactions are modified. This undoes the changes originally performed by the uncommitted transactions. The undo file is used to save the contents of these pages before recovery modifies them to prevent the changes performed by the uncommitted transactions from being lost. Before a subsequent transaction log backup is next applied to the database, the uncommitted transactions that were previously rolled back by recovery must be reapplied first. The saved changes in the undo file are reapplied to the database, and then the next transaction log is applied.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com