I have a production database hosted on SQL Server 2008R2 Standard that is replicated to a second server running the same version of SQL server via a subscription to the production database. The subcriber database is used for running SQL reports on the data. Deletes aren't replicated, so the reporting database is pretty large compared with the production published database, which is cleaned up weekly.
I'm planning on replacing the server that hosts the published database and was wondering what the best course of action was to ensure that the subscriber database doesn't loose any data. Is this a sensible outline of the steps to follow:
- Restore a copy of the latest backup from the old production database to the new server
- Publish this new database using the same settings as the old database
- Unsubscribe (or whatever the proper term for breaking the replication is) the reporting subscribed database from the old published database
- Subscribe the reporting database to the new publication
Is it as simple as that, or is there something I've missed that could turn around and bite me? The main thing I want to ensure is that the database used for reporting (the subscribed database) doesn't loose any data, and continues to receive new replicated data from the new database.