Synchronizing SQL Server environments

Home - Continuous X - Synchronizing SQL Server environments

When deploying changes through DTAP, you might need to sync your environments. For example, I sync my Acceptance environment before deploying a new increment, so I can easily see the effects of my new software on production data. To be honest, I’m not very good in these database management tasks[ref]I’ve worked once in an environment where these syncs were done manually. Of course I interchanged the two environments, ending up with production data being replaced with test data..[/ref] so I have to automate these sync tasks. Here’s how:

Getting the data across

Synchronizing two databases is not hard – especially when these databases are in fact data warehouses with scheduled batch ETL. My basic way of working here is:

  1. Back up databases on source environment
  2. Transfer backups across the network towards the target environment
  3. Restore databases on target environment, overwriting existing databases


When backing up databases, remind these need to be kept out of the regular backup line – you don’t want to interfere with regular incremental backups, but instead do a full backup that’s only meant to copy the database somewhere. So, remember to set “CopyOnly” to TRUE!

Below is my entire script for automated backup, move and restore across the network. All variables are set in the first 13 lines.

Recording Right User Rights

When databases are transferred to another environment, the user rights are transferred along. This is great for “real” backups, but not so much for our “copy only” usage here. In order to store which rights ought to be in place on which environment, I’ve put a small mechanism in place to record the original rights of the databases involved in this process.

The key artifact here is an environment-specific metadata database. Inside this database live five tables storing the users & rights as they should be in this specific environment. Also included is a stored procedure that updates the metadata tables:


That’s all, really.

Restoring Right Rights

In order to restore the rights as they should be, currently I’m still using a manual SQL script. It can be automated in two ways, but I haven’t found time for that yet.

  1. Call automatically after each sync (i.e. within the PowerShell-script)
  2. Enhance the script so that the same databases are used as the “update” stp uses[ref]I think this functionality is a matter of “scratch your own itch” – as I’m currently only handling my own databases, it doesn’t itch that much. Pull requests will be accepted though.[/ref]

Basically the “restore rights” SQL script looks like this:

Notice the MetadataDatabase and MyJustRestoredDatabaseName here. Obviously you need to replace that with resp. your metadata database-name and the database just synced by the sync-script.


No rocket science today, but some scripts I find rather useful and can be used pretty easily without much configuration in other environments. Besides, the ability to transfer production data to another (preferably thoroughly shielded & sandboxed) environment is an important building block for setting up automated regression testing…

All sources are on GitHub:


Latest Posts