Dynamically creation copy of database for testing

Question

Hi 2 all,

I am going to create integration tests for my application. I want that my integration tests will be executed not in production DB. I want that before test execution begin, my continues integration server (team city) creates copy of my database and integration tests will be executed on it. Any approaches or best practicies ?

EDIT:

I would like to automatizate this process (any links to articles or forums).

Answer 1

You probably don't want the build server touching the production DB (I certainly wouldn't).

I'd create a job to load up your database from a .bak file or from scripts (you can probably version this if it doesn't get too large) and run the job prior to running your tests.

Answer 2

Here are the steps that I would do

1. Backup and restore production database to the Integration server.
2. Remove/reset private information (credit cards, ssn, passwords) from the Integration database so that testers do not work with actual production data

Since you are using SQL Server, this is fairly easy to do using SSIS.

Steps:

1. Create SSIS Package
2. Add task to copy database or to backup restore database from Production to Integration
3. Add query tasks for each table cleanup operation
4. Save the package to SQL Server
5. Run job from within SQL Server or Create a job in SQL Scheduler to run at periodically

To learn about SSIS, a good book is SQL Server Integration Services Step By Step by Microsoft Press.

Good forums for SQL Server

• http://www.StackOverflow.com
• http://Ask.SQLServerCentral.com

Answer 3

I am currently working to complete the same task. I have installed nant on my machine, and then created the following folders

./servername/databasename/release
./servername/databasename/restore
./servername/databasename/rollback
./servername/databasename/test

The plan is then to create nant targets for each task (release, restore, rollback,test).

./servername/databasename/restore/000001.set_database_offline.sql
./servername/databasename/restore/000002.restore_database.sql
./servername/databasename/restore/000003.fix_orphaned_logins.sql
./servername/databasename/restore/000004.do_some_operation_example_upgrade_table_x.sql

I then create all my scripts with numeric prefixes, to control sequence of execution. The idea behind this is to create build tasks named to match my folders, so I can just execute commands listed below, which will quickly execute all the scripts in the directories.

nant restore
nant release
nant rollback
nant test

I am thinking I will need to enhance the process already to have separate tests for release and rollback, however this is a work in progress.

Let me know if you choose to follow the same path. Would be really cool to collaborate and share a simple repeatable process.

Reasons for doing this are: 1. Automation 2. Repeatability 3. Consistency 4. Add all code to Source Control

Goal being to be able to deploy and test across multiple environments, and do no code change while promoting code up to production.

Regards,

Answer 4

I have been doing exactly this for at least two years. This is not complex. I create my test database from a script, populate test data also from a script, all invoked from NUnit FixtureSetup step. No problems whatsoever.