Database Technologies‎ > ‎MSSQL‎ > ‎

Log Shipping

Of the three database restoration one is the standy mode, which
enable database to be in read only mode but we could apply the
additional transaction log files.
Similar to Oracle's SCN, MSSQL has LSN (Log Sequence Number),
any log entry made to the transaction log file has a LSN

When we restore a database in standby mode or a apply a transaction
 log file (.trn) to a database in standby mode, we specify a tuf file i.e
Transaction Log Undo File. This .tuf file contains information regarding
any modification that were made as part of incomplete transaction at the
time backup was performed.

Scenario 1

In a DR environment where we have log shipping configuration to standby database (Read Only Mode)
Every time we make a DR DRILL (scratch the changes) we have refresh (backup and restore) the database again on the DR to
make it in sync again

This often takes much time if the database backup size is too large.

Below mentioned steps outlines the procedure to minimize the the time for DR restoration after the DR Drill is over.

1) Shut down the DR server.

2) Take cold backup of the Databases
    master,msdb,User Databases,

3) Take backup of the tuf files that are used for standby  database.

Note : *.tuf files are very important since they contain the LSN information of the last applied transaction log file

4) Open the database in read write mode,
    Restore database with recovery

5) After the application team's activity is done, restore the cold backup in their respective location.

Paste back the tuf file in their location.
6) Done !!

2) Scenario 2
Switching Roles
1) Make sure dr is in sync , take a fresh tran backup make sure it gets applied to the dr
use the following query to check the log restoration

select * from log_shipping_monitor_secondary;
2) Take a last transaction log of the Production, and select the option () backup tail of the log and leave it in restoring state
3) Apply the log in the dr with recovery i.e read write.
4) Reverset the log shipping