I am designing a solution that utilizes replication from a front-end database to a back-end database. Historical data will be truncated from the front-end database in such a way that these truncation deletes will not be replicated - thus, the back-end database acts as a historical archive for the front-end database.
I am concerned about backup and restore scenarios for this environment.
Once we have begun truncating data from the front-end database, we will never be able to re-snapshot the database to rebuild the back-end database.
My concern comes if the servers are destroyed and the environment must be rebuilt. For SQL Server 2000, Books Online says:
"When you restore a backup of a replicated database to a server or database other than the one on which it was created, your replication settings cannot be preserved. For publishing databases and merge subscribing databases, a full restore of the database and logs is followed by an automatic removal of replication meta data from the database when the database or server you restore to differs from the one on which the backup was created."
Books Online for SQL Server 2005 has a similar statement. Does this mean that I would be unable to restore my front-end and back-end databases, and reestablish replication without taking a new snapshot? Does SQL 2005 provide any advantages for disaster recovery given this scenario? Does restoring with the KEEP_REPLICATION option override the behavior specified in the above BOL quote?
That isn't correct. You can restore a backup without preserving replication settings and then reconfigure replication in that environment. You would not want to issue a snapshot, so you would simply tell the replication engine that the subscriber already has the schema and data.
Now, there are two different behavior paths. With SQL Server 2000, you would tell it that the subscriber already has the schema and data "nosynch" and a subscription would be created, but changes would fail, because you would have to manually create the replication stored procedures that are called on the subscriber. But, once created, all of the data would flow normally.
With SQL Server 2005, you create the subscription with replication support only. This would not cause a snapshot to be sent to the subscriber, but it would create all of the necessary replication objects.
|||Hello,
I have read your comments about restoring backup in a diferent server. This is my case and I am really desesperated becuase I am totally unable to do it . I only have a backup from the old server and I have no idea about scripts procedures or programming I am only trying from the SQL SERVER Enterprise Manager. Please let me know if it does really exist the posibility.
Thanks a lot
Carlos
|||did you found any solution for this?
No comments:
Post a Comment