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.

MSSQL DR DRILL.

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