What is the easiest way to sync data between two databases on two different instances. I am not thinking replication. Just sync e.g. production database to test database from time to time.

  • Backup prod (copy_only) / restore to test? Which instance is 2008 and which is 2016? You cannot restore a backup of a 2016 database to 2008. – Scott Hodgin Oct 18 '19 at 10:30
  • It's from prod to test which is 2016 – myusu Oct 19 '19 at 11:13
  • If Test is a higher version, why not just use backup & restore? – HandyD Oct 21 '19 at 6:38
  • I don't want to restore db schema etc each time. Just want to run some kind of a script for copying data only. Is it possible to write a script for this purpose? – myusu Oct 28 '19 at 8:42

These are my suggestions.

Replication even if you said you won't use it. It's quite the easiest and best solution you can use for this. The replication is easy to setup, replicate fast and you don't have to invent the wheel again. If you just weird about locking, you may try to set the ISOLATION LEVEL to READ_COMMITTED_SNAPSHOT. This will use up a part of your tempdb, but your table is always read- and writeable and the replication can work in the background. See the example below:


CDC (Change Data Capture) can also be a solution. But this way you need to build nearly everything on your own. And I've made the experience that CDC can be a fragile thing in some circumstances. CDC will capture all data on a watched table (you need to specify each watched table manually). Afterwards you'll get the value before and the value after an INSERT, UPDATE or DELETE. CDC will hold back those information for a period of time (you can specify it on your own). The approach could be to use CDC on certain tables you need to watch and manually replicate those changes to the other database. By the way, CDC uses the SQL Server Replication under the hood too. ;-)

Warning: CDC will not be aware of DDL-changes. This means, if you change a table and add a new column, CDC will watch the table but ignore all changes to the new column. In fact it only records NULL as value before and value after. You need to reinitialize it after DDL-Changes to a watched table.

  • I have a sql 2016 test server and a 2008 prod server. Just want a simple way of sync from prod to test once in a while. No replication needed. – myusu Oct 19 '19 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.