Conceptual Inquiry

Currently we take a full backup of a database once a month copy it to a USB drive carry that drive to a different location. We then remove all replication from the target server restore the backup over top of the database located there. Then go through the process of recreating replication again. The process is futher frustrated by the fact that due to the complexity of the database we are unable to snap shot create it. So a separate backup has to be done as part of recreating the replication.

I am looking for a way to script the changes since the last full backup of a database in a production environment or simple script the differences between two time periods of the same database. Then apply those changes to a development environment. These environments are in completely different locations with only the internet or sneaker net to link them. The size of the database is significant (over 150GB) The number of daily changes also fairly significant (2-4GB) as measured by log file backups.

This scenario is complicated by the fact that the database in question is published to a number of subscribers.

While a log shipping scenario is typically thought of for this scenario. The problem is every time the target database is accessed for any purpose it breaks the log shipping configuration.

Replication is not feasible in this scenario because of bandwidth limitations between the two sites.

I am curious if it is conceptually possible to be able to script out the days changes to one published database via SQL Data compare and apply the resulting script to a copy of the database on a different server also participating as a published database?

Can SQL Data compare be made aware that it is scripting for a replicated environment and hence use only data commands that would work with the restrictions SQL replication creates?



Regards
Scott Magnan

Comments

  • Michelle TMichelle T Posts: 566 Gold 1
    Currently Data Compare is not specifically aware of replication in any way. Making Compare and Data Compare replication-aware is on our list of things to do, though. Could you give more detail about your requirements (e.g. what data commands that Data Compare currently generates would break your replication setup)?
    Software Developer
    Redgate Software
Sign In or Register to comment.