Best practise for copying a database from production

Question

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?

Answer 1

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.

Answer 2

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.