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.
· 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
