At the last user group that I attended, a question was raised adbout restoring databases involved in replication and maintaining the replication settings.  I mentioned that the RESTORE command has a KEEP_REPLICATION option that can be used in this scenario but thought I’d go into a little more detail here.

 Typically when you restore a SQL Server database, replication settings are removed during the restore process.  This allows you to do a database recovery to a dev/test/DR environment and not have to worry about replication settings causing issues in a non replicated environment.  In many scenario’s however you may need to restore a replicated database and maintain these replication settings so that you can re-initialize replication after the restore.  The KEEP_REPLICATION option allows replication settings to be maintained after a database is recovered.  Syntax is below:

 RESTORE DATABASE northwind FROM DISK=’d:\backups\northwind.bak’ WITH KEEP_REPLICATION

For more information, Microsoft has an MSDN article that goes through the entire Backup and Recovery strategy for databases involved in replication.  There is quite a bit more work here than you may initially think, so be prepared.

http://msdn.microsoft.com/en-us/library/ms151152.aspx

If anyone has any real-world scenarios around backing up and restoring replicated databases and some of the challenges that you’ve faced, I’d love to hear them in the comments section.