Red Gate forums :: View topic - Comparing and updating a DB Clone with the Production DB
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Compare 10
SQL Data Compare 10 forum

Comparing and updating a DB Clone with the Production DB

Search in SQL Data Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
TimBee



Joined: 15 Feb 2014
Posts: 1

PostPosted: Sat Feb 15, 2014 1:47 pm    Post subject: Comparing and updating a DB Clone with the Production DB Reply with quote

Hi. Hope you guys and girls can tell me how to proceed from here:

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.
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 921
Location: Red Gate Software

PostPosted: Mon Feb 17, 2014 12:48 pm    Post subject: Reply with quote

Thank you for your forum post.

SQL Data Compare does not have the ability to take a snapshot of a database.

Its sister product SQL Compare has the ability to create a snapshot which contains just the database schema, no data is included.

If I were in your shoes, I would take a full backup of the current webserver database and restore the backup file on the new server.

Next using SQL Data Compare, compare the current production database with the one you have just restored and then deploy the changes that have taken place since the full backup was taken. Ensure that the option "Show identical values in results" is not enabled. Further you can also configure a 'Where' clause filter for each table, to reduce the amount of data to be compared.

Another alternative is to use SQL Backup. Take a full backup of the current webserver database and restore the backup file on the new server using either WITH NORECOVERY or WITH STANDBY recovering options.

Perform log backups of the source database, restore these to the new server / database sing either WITH NORECOVERY or WITH STANDBY recovering options. until you are happy that they are in sync. Then restore the last log backup WITH RECOVERY to bring the new server / database on line.

Many Thanks
Eddie
_________________
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group