Log shipping with Standby/ReadOnly

Run the below on the Primary

BACKUP DATABASE LogRestoreDB
TO DISK = '\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_full1.bak'
WITH INIT, FORMAT,COMPRESSION

INSERT INTO t1(Id)
VALUES (1)
BACKUP LOG LogRestoreDB
TO DISK = '\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log1.trn'
WITH INIT, COMPRESSION

INSERT INTO t1(Id)
VALUES (2)
BACKUP DATABASE LogRestoreDB
TO DISK = '\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log2.trn'
WITH INIT, COMPRESSION

INSERT INTO t1(Id)
VALUES (3)
BACKUP DATABASE LogRestoreDB
TO DISK = '\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log3.trn'
WITH INIT, COMPRESSION

Run the below on the Secondary to restore the database in standby/read-only mode


ALTER DATABASE LogRestoreDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

USE [master];
RESTORE DATABASE [LogRestoreDB]
FROM DISK = N'\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_Full.bak'
WITH STANDBY = '\\SQLSERVER01\SQLBackup\Standby\LogRestoreDB_Standby.tuf';
GO

--Check the table t1
-- SELECT * from t1;

USE [master];
RESTORE DATABASE [LogRestoreDB]
FROM DISK = N'\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log1.trn'
WITH STANDBY = '\\SQLSERVER01\SQLBackup\Standby\LogRestoreDB_Standby.tuf';
GO

--Check the table t1
-- SELECT * from t1;

USE [master];
RESTORE DATABASE [LogRestoreDB]
FROM DISK = N'\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log2.trn'
WITH STANDBY = '\\SQLSERVER01\SQLBackup\Standby\LogRestoreDB_Standby.tuf';
GO

--Check the table t1
-- SELECT * from t1;

USE [master];
RESTORE DATABASE [LogRestoreDB]
FROM DISK = N'\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log3.trn'
WITH STANDBY = '\\SQLSERVER01\SQLBackup\Standby\LogRestoreDB_Standby.tuf';
GO

--Check the table t1
-- SELECT * from t1;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: