Restoring Backups of Replicated Databases to the Same Server and Database
When you create a backup of a database, Microsoft® SQL Server™ 2000 makes a 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 2000 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.
Publication Databases
For both transactional and merge publication 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. If the failure you are recovering from required you to completely re-create the database you are restoring into, run sp_replicationdboption or enable the database for transactional and merge publishing before restoring your backup.
Caution If you do not run sp_replicationdboption, your replication settings will be lost during the restore operation.
Distribution Databases
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 created. A coordinated restore of both databases is often required to preserve transactional integrity in your replication scenario. You may want to consider including a coordinated backup and restore of your replication working directory associated with the publishing database. This can reduce the amount of time required to resynchronize Subscribers in snapshot and transactional replication scenarios.
Similar to 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 restore a distribution database only when you are restoring a publishing database, and always 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.
Subscription Databases
For transactional replication, subscription databases contain the table MSreplication_subscriptions, which stores data indicating the last transaction received at the Subscriber. This table is included automatically when a subscribing database is backed up.
After a restore or attach of a transactional subscription database, you should run sp_vupgrade_subscription_tables to ensure that all required objects are created and are the correct version. If you do not run sp_vupgrade_subscription_tables, objects necessary for replication may not exist in the subscription database.
Merge subscription 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 merge publishing databases should also be applied when working with merge subscribing databases.