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'
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.
Regards
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
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.