Log Shipping – DR Drill Step by Step

DR Drill – without configuring reverse log shipping

Server setup:
Primary server: HostName\SQLINSTPRI
Secondary server: HostName\SQLINSTSEC
Network(Shared) Path: \\HostName\Primary
Backup Path: D:\LogShipping\Secondary
Primary Database Name: AdventureWorks
Secondary Database Name: AdventureWorks_Test (Standby/Read-Only)

NOTE: Before performing this activity, application team has to stop the hits to the primary and secondary database. Verify the session in the primary server using sp_who2

Following are the step for Log shipping DR Drill:

STEP 1: Disable the following jobs in the Primary Server.

Logshipping DR drill

STEP 2: Execute the Copy and Restore jobs in the Secondary Server to ensure all backed up Transactional logs file from primary server are copied and restored in the Secondary server database.

copy and restore jobs

STEP 3: Disable the following jobs in Secondary Server.

disable secondary server

STEP 4: On the primary Server, generate a transactional log backup with NORECOVERY.

BACKUP LOG [AdventureWorks] TO  DISK = N'D:LogShippingPrimaryAWLastLogBackup.trn' WITH  NO_TRUNCATE , NOFORMAT, NOINIT,  NAME = N'AdventureWorks-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  NORECOVERY , COMPRESSION,  STATS = 10
GO

STEP 5: Move the transactional log file taken in previous STEP to the secondary server.

STEP 6: Restore the Last Transactional log backup performed in STEP 3 in to the Secondary Server database with RECOVERY.

RESTORE LOG [AdventureWorks] FROM  DISK = N'D:LogShippingSecondaryAWLastLogBackup.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

NOTE: Now the Secondary Server database will be online. Inform application team to start pointing the application to the secondary server database and starting the testing. On completion of application testing, now follow the STEP’s to rollback.

STEP 7: On the Secondary Server, generate a transactional log backup with NORECOVERY.

BACKUP LOG [AdventureWorks] TO  DISK = N'D:LogShippingSecondaryAWSecLastLog.trn' WITH  NO_TRUNCATE , NOFORMAT, NOINIT,  NAME = N'AdventureWorks-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  NORECOVERY , COMPRESSION,  STATS = 10
GO

STEP 8: Move the transactional log file taken in previous STEP to the Primary Server.

STEP 9: Restore the Last Transactional log backup in to the Primary Server database with RECOVERY.

RESTORE LOG [AdventureWorks] FROM  DISK = N'D:LogShippingSecondaryAWSecLastLog.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

STEP 10: Enable the following jobs in the Primary Server.

Enable primary server

STEP 11: Enable the following jobs in Secondary Server.

Enable secondary server

Monitor the backup job in Primary Server and Copy & Restore jobs in Secondary Server for some time to ensure the logs are getting shipped from Primary to Secondary without any deviations.

 

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply