Share via

  • Article

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

  1. Execute the BACKUP DATABASE statement to create the full backup.

  2. Execute the BACKUP LOG statement to create a transaction log backup.

  3. Repeat Step 2 for each transaction log you want to create over time.

To set up and maintain the standby server

  1. Execute the RESTORE DATABASE statement using the NORECOVERY clause to restore the full backup created in Step 1 on the primary server.

  2. 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 faster performance, when you apply multiple logs, WITH NORECOVERY should be used. Sometime, a standby file is needed only as a read-only, failover copy that you can use if the principal is taken offline.

  3. Repeat Step 2 for each transaction log created on the primary server.

To bring the standby server online (primary server failed)

  1. If possible, back up 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).

  2. 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 faster performance, when you apply multiple logs, WITH NORECOVERY should be used. For an example of when to use WITH STANDBY and when to use WITH NORECOVERY, see below.

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