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 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

Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook