Restore transaction log with standby in SQL Server

Restore transaction log with standby option leaves the database read only mode. The uncommitted transaction are undone and saved in a file so that recover effects can be reversed when database is restored with recovery option.

A use case for this option is the Log shipping where a secondary database can be left in standby mode. The read load as in reports and large queries can be run on the standby secondary server so as to share the primary server load.

The T-SQL to restore transaction log in STANDBY mode is given below.

RESTORE LOG AdventureWorks FROM DISK='e:\ahmad\adventureworks_log_10.trn' 
WITH STANDBY='E:\ahmad\standby_file.bak'

restore transaction log with standby

   

The AdventureWorks database is in STANDBY read only mode as shown in above image.

The read operations against this database will succeed however we’ll get error when writing to this database as shown below.

2_restore transaction log with standby
Regards

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

   

One Comment on “Restore transaction log with standby in SQL Server”

  1. I have tried this with SQL Server Enterprise Edition 2016 and it takes the database out of standby mode. All I want to do is keep the database in Standby mode and restore transaction logs to it. Many thanks, Derek.

Leave a Reply

Your email address will not be published.