How to: Set Up, Maintain, and Bring Online a Warm Standby Server (Transact-SQL)
Setting up a standby server generally involves creating a full backup and periodic transaction log backups at the primary server, and then applying those backups, in sequence, to the standby server. The standby server is left in a read-only state between restores. When the standby server needs to be made available for use, any outstanding transaction log backups, including the backup of the active transaction log, from the primary server, are applied to the standby server and the database is recovered.
To create backups on the primary server
Execute the BACKUP DATABASE statement to create the full backup.
Execute the BACKUP LOG statement to create a transaction log backup.
Repeat Step 2 for each transaction log you want to create over time.
To set up and maintain the standby server
Execute the RESTORE DATABASE statement using the NORECOVERY clause to restore the full backup created in Step 1 on the primary server.
Execute the RESTORE LOG statement using the STANDBY clause to apply each transaction log created in Step 2 on the primary server. Specify the name of the undo file that contains the contents of data pages before uncommitted transactions affecting those pages were rolled back.
Note
For better performance, when you are applying multiple logs, WITH NORECOVERY should be used. Sometimes a standby file is needed only as a read-only, failover copy so that it can be used if the principal is taken offline.
Repeat Step 2 for each transaction log created on the primary server.
To bring the standby server online (primary server failed)
If possible, backup the currently active transaction log (the tail of the log). To create a tail-log backup when the database cannot start up normally, execute the BACKUP LOG statement on the primary database using WITH NO_TRUNCATE. This is the last transaction log backup that is applied to the standby server when the standby server is brought online. For more information, see How to: Create a Transaction Log Backup (Transact-SQL).
Execute the RESTORE LOG statement using the STANDBY clause to apply all transaction log backups, including the active transaction log backup created in Step 1, that have not yet been applied to the standby server.
Note
For higher performance, when applying multiple logs, WITH NORECOVERY should be used. For an example of when to use WITH STANDBY and when to use WITH NORECOVERY, see below.
Execute the RESTORE DATABASE WITH RECOVERY statement to recover the database and bring up the standby server.
Example
This example sets up the MyAdvWorks database on a standby server. The database can be used in read-only mode between restore operations.
-- Restore the initial full backup on the standby server.
USE master
GO
RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyAdvWorks
FROM MyAdvWorks_log1
WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the next transaction log backup.
RESTORE LOG MyAdvWorks
FROM MyAdvWorks_log2
WITH STANDBY = 'c:\undo.ldf'
GO
-- Repeat for each transaction log backup created on the
-- primary server.
--
-- Time elapses.. .. ..
--
-- The primary server has failed. Back up the
-- tail of the transaction log on the primary server.
BACKUP LOG MyAdvWorks
TO MyAdvWorks_log3
WITH NO_TRUNCATE
GO
-- Apply the final (active) transaction log backup
-- to the standby server. All preceding transaction
-- log backups must have been already applied.
RESTORE LOG MyAdvWorks
FROM MyAdvWorks_log3
WITH NORECOVERY ''
GO
-- Recover the database on the standby server,
-- making it available for normal operations.
RESTORE DATABASE MyAdvWorks
WITH RECOVERY
GO
See Also
Tasks
How to: Back Up the Transaction Log When the Database Is Damaged (Transact-SQL)
Concepts
Other Resources
RESTORE (Transact-SQL)
Restoring a Database to a Point Within a Backup
SQL Server Management Studio Tutorial