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.
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.
Repeat Step 2 for each transaction log created on the primary server.
To bring the standby server online (primary server failed)
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).
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.
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.
Execute the RESTORE DATABASE WITH RECOVERY statement to recover the database and bring up the standby server.
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