Backing up Database AND replication configuration?

Question

In SQLServer 2005, is it possible to migrate a database from a workstation to another and also take the Replication configuration with it?

I mean, besides creating scripts.

Answer 1

As noted in the Microsoft article Backing Up and Restoring Replicated Databases:

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.

Answer 2

Replication and SQL Agent configuration is stored in the MSDB system database. If the server you are migrating to has the same version of SQL Server as the source server, you should be able to restore the msdb database along with your user database to get the replication configuration back. You will also need to bring over the security information from the source server to the target server. This information is stored in the master database, so you may be able to backup and restore the master database on the new server, or script out the logins and restore them on the new server. Check out these articles for more information:

You cannot restore system database backups to a different build of SQL Server http://support.microsoft.com/kb/264474

How to transfer logins and passwords between instances of SQL Server http://support.microsoft.com/kb/246133

Managing Metadata When Making a Database Available on Another Server Instance http://msdn.microsoft.com/en-us/library/ms187580.aspx

Answer 3

If you're moving a publisher:

-Backup up the SMK, publication database, msdb, distribution, and the master database.

-Restore them on the new server with the same name as the old server.

-Ensure you use the keep_replication switch when doing the restore.

If you're moving a subscriber:

-Backup up the SMK, master, msdb, and subscription database.

-Restore them on a new server with the same name as the old server.

-Ensure you use the keep_replication switch when doing the restore.