You should protect your Microsoft® SQL Server™ databases against catastrophic loss by making regular and complete backups. If the databases or data should later become corrupted or otherwise unavailable, you can recover your data by restoring the lost database from the backup. In addition to software backups, you should also consider using hardware-based fault tolerance such as redundant arrays of independent disks (RAID), server clustering, and Microsoft Windows® disk mirroring and duplexing.
In addition to the regular backup and restore guidelines and procedures for SQL Server, there are additional considerations for backing up and restoring the databases involved in replication. These consideration vary according to the type of replication implemented and the role the server performs in replication: Publisher, Distributor, or Subscriber.
These considerations can be assembled into specific strategies for:
SQL Server replication has several features that be used as an integral part of your backup and restore strategy:
Scripting the setup and topology of replication in your application provides a one-step method for reestablishing replication.
The use of the automatic resynchronization on validation failure feature allows you to check if a Subscriber is in the correct state after you have restored the dump of the Publisher or Distributor. The feature also allows you to easily validate that the Publisher and Subscriber are in synchronization after restoring either one.
The snapshot files are stored at the Distributor and can be backed up at the same time as the database. If the snapshot files are very large, restoring instead of regenerating the snapshot files can save time if you have to restore a Distributor.
The snapshot files are stored at the Distributor and enable you to resubscribe to a publication without having to generate a new snapshot at the Publisher.
If you back up Subscribers, you can set the retention period at the Distributor so that the Subscriber can be resynchronized automatically.
| Backing Up and Restoring Databases | Configuring SQL Server Failover Support |
| RAID | About Windows NT-based Disk Mirroring and Duplexing |