Using Warm Standby Servers

When the primary server of a log shipping configuration initially becomes unavailable, it is unlikely that all the secondary databases on the secondary servers are fully synchronized. Some transaction log backups created on the primary server may not yet have been applied to the secondary server. Also, changes to the databases on the primary server may have occurred since the last transaction log backup. Before using the standby copies, synchronize the primary databases with the standby copies and bring the standby server online by following these steps:

  1. Apply to the standby server, in sequence, any unapplied transaction log backups created on the primary server.
  2. Create a backup of the active transaction log on the primary server and apply the backup to the database on the standby server. The backup of the active transaction log when applied to the standby server allows users to work with an exact copy of the primary database as it was immediately prior to failure (although any uncommitted transactions are permanently lost). For more information, see Working with Transaction Log Backups.
  3. Recover the databases on the standby server. This recovers the databases without creating a standby file, making the database available for users to modify.
  4. If the primary server is undamaged, as in the case of planned maintenance or upgrades, you can back up the active transaction log with NORECOVERY. This leaves the database in the restoring state. For more information, see Tail-Log Backups.
  5. Optionally, update the primary server with transaction log backups from the secondary server. Then you can switch back to the primary server without having to backup and restore the secondary database. For more information, see Applying Transaction Log Backups.

To reduce your resource requirements, you can store backups of databases from several primary servers on a single standby server. For example, consider a department that has primary servers, each server running a mission-critical database system. The computing environment of this department is configured to minimize the possibility of multiple primary servers failing at the same time. Recent database backups from each of the five primary servers are copied onto a single, common standby server. To address the possibility of concurrent failures affecting primary servers, the standby server is of higher specification than the primary servers. In the event of a disaster on any of the primary servers, backups of its databases can be restored and recovered on the standby server.

See Also

Other Resources

Log Shipping
Restoring a Database to a Point Within a Backup
RESTORE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance