Moving Replicated Databases to a replacement SQL Server

Any advice that can be offered on this subject would be much appreciated.

We are debating whether to backup/restore the replicated databases or simply copy the mdf and ldf files accross and Attach them. The question is whether the replication information is retained for the publshed objects when the files are copied across? Alernatively, must we use backup/restore with KEEP_REPLICATION set for the restore?

We also plan to back up and restore the distribution, publication, master, msdb etc databases as well as rename the SQL Server to have the same name as the original.

Do these things need to happen in any particular order?

Does the timing of the renaming of the SQL Server matter?

At least some of the these operations (restoring the master database and renaming the SQL Server) need to be done in single user mode. Is is best to restore all the system databases in single user mode?

Lastly, is it necsssary (from a SQL Server point of viewpoint) to rename the computer to have the same name as the SQL Server?

Thanks,

Dick Campbell

You can do either of the options. However there are pros and cons for each of the approaches. I am assuming here that you are doing backup/restore or copy of mdf/ldf of the publisher server (But it applies to the subscriber also).

1. Backup/restore.

You can backup and restore the databases on a different (secondary) server. However you cannot expect that replication will work automatically when your primary server goes down. You will need to resetup replication on ths secondary box.

2. Copy of mdf/ldf.

This can be done too. However the steps you will need to follow are:

a. Copy the files from prmary to a backup location

b. Lets assume your primary now goes down

c. Now you will need to rename the secondary server to the primary server, then install SQL Server and replace the mdf/ldf from on this machine with the ones from the backup location. Replication should then work fine.

My above recommendations defintely apply for Merge replication. They may also apply to Transactonal rpelication but there could be some caveats.

You should defintely try out the scenarios on your test machines.