When you create a backup of a database, Microsoft® SQL Server™ makes a complete copy of all user tables and system tables (including sysobjects) in the current database. It also makes a complete copy of the log file(s) for the current database, including everything past the last log read transaction.
When you restore a database to the same server and database from which it was backed up, SQL Server does a full restore of the database and log. SQL Server then reads the master.dbo.sysdatabases.category column for the restored database to determine if any replication settings stored in the target database should be preserved.
For both transactional and merge publishing databases, replication is preserved if the sysdatabases.category column is set to indicate the database is enabled for publishing. For transactional and snapshot publishing databases, the category bit is set to 1. For merge publishing databases, the category bit is set to 4.
In most cases, restoring a backup to the same server and database from which it was created will preserve your replication settings. However, if the failure you are recovering from required you to completely re-create the database you are restoring into, you will need to run sp_replicationdboption or, otherwise, enable the database for transactional and merge publishing before restoring your backup. If this step is not taken, your replication settings will be lost during the restore operation.
A single Distributor can store many distribution databases; up to one per Publisher served by the Distributor. It is important that when a publishing database is backed up, a coordinated backup of its associated distribution database is made. Likewise, a coordinated restore of both databases will often be required to preserve transactional integrity in your replication scenario. Also, you may want to consider including a coordinated backup and restore of your replication working directory associated with the publishing database. This can help to reduce the amount of time required to resynchronize Subscribers in snapshot and transactional replication scenarios.
Like publishing databases, distribution databases cannot be restored to any location. Because of several database and server name dependencies among replication Publishers, Distributors, and Subscribers, you must restore to the same server and database you created the backup from to ensure proper resumption of replicated data flow. You should only restore a distribution database when you are restoring a publishing database, and always only to the same server and database. After restoring the distribution database, review the replication agent profiles to confirm they are set as required by the application.
For transactional replication, subscription databases contain the table Msreplication_subscriptions that stores data indicating the last transaction received at the Subscriber. This table is automatically included when a subscribing database is backed up.
Merge subscribing databases are internally tracked as a type of publishing database as well. For this reason, the same considerations taken when planning for backup and restore of a merge publishing databases should also be applied when working with merge subscribing databases.
| MSreplication_subscriptions | sysdatabases |
| sp_replicationdboption | sysobjects |