How to set up, maintain, and bring online a standby server (Transact-SQL)

How to set up, maintain, and bring online a standby server (Transact-SQL)

Setting up a standby server generally involves creating a database 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 database 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 STANDBY clause to restore the database backup created in Step 1 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.
   
   
2. Execute the RESTORE LOG statement using the STANDBY clause to apply each transaction log created in Step 2 on the primary server.
   
   
3. Repeat Step 2 for each transaction log created on the primary server.

To bring the standby server online (primary server failed)

1. Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back up the currently active transaction log. This is the last transaction log backup that will be applied to the standby server when the standby server is brought online. For more information, see How to create a backup of the currently active transaction log.
   
   
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.
   
   
3. Execute the RESTORE DATABASE WITH RECOVERY statement to recover the database and bring up the standby server.

Examples

This example sets up the MyNwind database on a standby server. The database can be used in read-only mode between restore operations.

-- Restore the initial database backup on the standby server.
USE master
GO
RESTORE DATABASE MyNwind
   FROM MyNwind_1 
   WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
   FROM MyNwind_log1
   WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the next transaction log backup.
RESTORE LOG MyNwind
   FROM MyNwind_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 
-- active transaction log on the primary server.
BACKUP LOG MyNwind
   TO MyNwind_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 MyNwind
   FROM MyNwind_log3
   WITH STANDBY = 'c:\undo.ldf'
GO
-- Recover the database on the standby server, 
-- making it available for normal operations.
RESTORE DATABASE MyNwind
   WITH RECOVERY
GO

See Also

RESTORE

Restoring a Database to a Prior State

Using Standby Servers