Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Currently after releasing a new major or minor version of our application, we take a copy of the production database and perform the below process:

  1. Copy production database to test server
  2. Obfuscate the data and setup the test users
  3. Switch the old test database with the obfuscated new database
  4. Repeat for the development database.

The intention of this is to ensure all our environments are as close to one another as possible. This originated as a manual process but due to increasing complexity we've started to automate it.

However what is the best way to automate a post deployment process such as this? Our currently automation is a bespoke c# program - however, would an SSIS package be more appropriate? Or is there a tool out there that is made for the job?

share|improve this question
up vote 3 down vote accepted

SSIS could certainly take care of this for you but as with many things there are several ways to complete the task.

1) You have several options within SSIS to complete the copy process, Copy Database is one of them. You could backup the database and restore it using a SQL Script. You may need to use the file system task to move the backup file but that depends on the configuration of your infrastructure.

2) Easily taken care of in SSIS by executing a no. of scripts against a target database.

3) Again, easily completed by running a SQL script against the target server.

4) You could use package configurations to specify a different environment/connection strings very easily.

I hope that's some help.

share|improve this answer

You could also use a SQL job with the SQL agent.

  • Backup
  • Restore
  • Obfuscate restored database
  • Restore

Note: be careful with the backup database operation, use the "copy only" option otherwise you might break the backup chain when differential backups are used.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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