Comparing and updating a DB Clone with the Production DB
I have a live production webserver running MS SQL. The Database is +50GB in size. I need to move this webserver to another hosting provider so I've made a clone 2 weeks ago and I'm done now with cleaning up the clone.
Next step is to update the SQL Database on the clone with the changes made on the production server during the last 2 weeks.
Limitations: Upload/download speed between the two boxes: transferring 50GB will take a very long time and I can't afford that amount of downtime on the production box.
What I've tried so far is this:
SQL Compare: make a Snapshot of the cloned DB with "Red Gate Snapper". Transferred the Snapshot to the Production Server and ran SQL Compare on the Production Server using the Snapshot as the Target. The Schema of the Production Database and the Cloned one were identical.
SQL Data Compare: I also started this on the Production Server. I Generated a SQL Script out of the Snapshot I've used earlier for "SQL Compare" and used the Script Folder as the Target. The Data Comparison took a lot of time to complete and generated a massive amount of Temporary Data (about the size of the Database itself I guess).
After completion it looked like the Target Database was missing everything, which makes sense since I compared it against a Database Schema without any actual data in it.
A solution would be (I think) that I would be able to create a SQL Data Compare "Snapshot" of the Cloned Database, which in some way is limited in size so I could
upload it to the Production Server to run a comparison against the Production Database. After that is done I end up with the data of the last two weeks: SQL Data Compare will then create an Export/Script that I can transfer to the Cloned Server to update the DB with the missing data.
Is this possible? And if yes: What was I doing wrong/missing. As you probably already noticed: I'm not a SQL Expert.
My worst enemy is data transfer because of the limited transfer speed and size of the database.
Thanks a lot for any info you may have to help me out.