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
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
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?
16 People are following this question.
