Backing Up and Restoring Replicated Databases

Replicated databases require special attention with regards to backing up and restoring data. This topic provides introductory information and links to further information on backup and restore strategies for each type of replication.

Replication supports restoring replicated databases to the same server and database from which the backup was created. If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must recreate all publications and subscriptions after backups are restored.

Replicated databases and their associated system databases should be backed up regularly. Back up the following databases:

• The publication database at the Publisher

• The distribution database at the Distributor

• The subscription database at each Subscriber

• The master and msdb system databases at the Publisher, Distributor and all Subscribers. These databases should be backed up at the same time as each other and the relevant replication database. For example, back up the master and msdb databases at the Publisher at the same time you back up the publication database. If the publication database is restored, ensure that the master and msdb database are consistent with the publication database in terms of replication configuration and settings.

If you perform regular log backups, any replication-related changes should be captured in the log backups. If you do not perform log backups, a backup should be performed whenever a setting relevant to replication is changed. For more information, see Common Actions Requiring an Updated Backup.