Tuesday, September 13, 2011

How to manually put Log Shipping secondary database into readonly mode

Beside choose "standby mode" using SSMO Transaction Log Shipping interface, below is the T-SQL way to change the secondary mode from "restoring" to "readonly".


-- The T-SQL Script tested in SQL Server 2008 R2

-- Put in to readonly mode, suggest to temporary disable the restore agent,
-- otherwise the agent will automatically put it in "restoring" mode.

RESTORE LOG [Northwind]
WITH STANDBY = N'C:\backup2\ROLLBACK_UNDO_Northwind.BAK'
GO



-- Put back into recovery mode

RESTORE LOG [Northwind] WITH NORECOVERY
GO