Saturday, August 25, 2007

SQL Server Standby Recovery

Recover can be done with a similar command as following:

restore log {db_name} from DISK=N'...\{db_name}_tlog_{datetime}.TRN' with standby = '...\UNDO_{db_name}.DAT'

Warning: Do not lost undo file. It is required to apply next log.
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

If prod-standby database files are at different paths/drives and new datafiles are added to prod database, log shipping will fail with the following error:

Device activation error. The physical file name ...\MSSQL\DATA... may be incorrect.

You should apply these logs manually by moving logical datafiles to valid physical locations at standby server:

sp_resetstatus 'db_name'
restore log db_name from DISK=N'...\{db_name}_tlog_{datetime}.TRN' with standby = '...\UNDO_{db_name}.DAT',
MOVE '{Newly_Added_Datafile_Logical_Name}' to '{Valid_Path}\{Physical_Datafile_Name}',
MOVE '{Newly_Added_Datafile_Logical_Name}' to '{Valid_Path}\{Physical_Datafile_Name}',
MOVE '{Newly_Added_Datafile_Logical_Name}' to '{Valid_Path}\{Physical_Datafile_Name}',

No comments: