How to synchronize the data from the Production environment to Development, Testing and Staging Environments for SQL Server? (Republished)

One of the most common DBA task is to synchronize data to Development, Testing, and Staging environments. Normally this is a simple task of backup and restore or using a data synchronization tool like the data compare in visual studio. BUT with a new version of an application on its way out from Development to Production, there might be schema changes to the database, and your task as a DBA becomes harder. How can you synchronize data without destroying the new schema?

Your option is often to use SSIS, a data compare tool or T-SQL scripting and mostly manually write the code needed to do the synchronization. In my option there is a better option if you use source control for both application code and database schema and using builds.

Solution:

· Backup production environment

· Loop over all your environments

   o Restore database to environment

   o Start a build of the application to the environment

  o Deploy your build of the application to the environment

How can you implement this?

There are some ways; one approach is to use a SQL Server agent job with SSIS to handle the task of Backup, Looping over environments and restoring the database. A PowerShell script can be used to start the build of the application and database schema. PowerShell could possibly also be used to deploying if the build itself does not handle that.

One you have this automated you will save a lot of time and make Developers and QA people happy, since they always have up-to-date data from the production environment in the environment they need. This will greatly help them test and debug the application.