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

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *