question

zillabaug avatar image
zillabaug asked

what is the easiest way of getting database in sync

whats the easiest way of making sure that data in production database is in sync with test database hosted on the same server? Currently, my test database is not in sync with my production database.My goal is to make sure that the data in test is up-to-date. Any suggestions? I use SQL server 2005
replicationadministrationmigrationdatabase-mirroringsql server 2005
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thanks guys
0 Likes 0 ·
MAXKA avatar image
MAXKA answered
Method1 You can use table diff utility to find and compare the data. Please refer to below article for how to use it: http://blogs.msdn.com/b/repltalk/archive/2010/02/21/how-to-run-tablediff-utility-for-all-replicated-published-tables-in-sql-2005-or-sql-2008.aspx Method 2 1. Add the destination server as a "Linked Server" 2. Use "EXEC msdb.dbo.sp_send_dbmail" along with "@attach_query_result_as_file =1" 3. Create an SSIS job which will execute the email SP for both the servers So, this is how you can get get two emails which has query results attached to it. And then comparing the text files completes the db sync check. Method 3: You can make use of thord party tools like Datacompare from Redgate or dbforgedatacompare: link :- http://www.devart.com/dbforge/sql/datacompare/?utm_source=datacomarereview&utm_medium=download&utm_campaign=cc_SQL_Authority
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
My first recommendation is to not have your test database on the same server as your production database. I'd recommend to get it moved off to another piece of hardware. In regards to getting it sync'd, if you don't need real-time level syncing, I'd recommend tools such as Red Gate's Data Compare or SSDT within Visual Studio. I prefer Red Gate's tool over VS, but they would both work. These would allow you to do a point in time syncing. Red-Gate: http://www.red-gate.com/products/sql-development/sql-data-compare/ SSDT: https://msdn.microsoft.com/en-us/data/tools.aspx Will point in time syncing work for your needs or do you want it real time?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Point in time syncing would be fine.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.