Database Testing Environment + TestData + QA Setup

Database Testing Environment + TestData + QA Setup


Database is SQL 2005 and Front End is in C#
----------------------------------------------------

I'm looking for a good strategy to save my data that I use for testing. I
made a set of good test data, and backed it up. I write my programs,
and do a round of testing on Database, and then when I want to start fresh
again, I need to do restore from my test data backup.

One way to achieve this would be DROP THE DATABASE and RESTORE THE PREVIOUS
VERSION OF the DATABASE for every new test.. This would be quite a costly
operation , since it would take a lot of system resources and lot of system
IO and is not the most effective way to do it. (Imagining that there are
around 100 tests and for each test ,dropping and restoring the database might
not be a good idea)

Another approach to this would be to write .sql scripts which delete the
data and then reload the intial data freshly.

Another approach to this would be to maintain a history table for each table
in the database and whenever there is a change in the particular database
table due to DATABASE OPERATIONS (DML) like CREATE, RESTORE , UPDATE ,
DELETE , then depending on the entry in the history table, we can rollback
that particular operation. For this the history table has to record , what
kind of operation has taken places (like whether the user is doing a UPDATE
or CREATE or DELETE etc) and then try to reverse (rollback) that particular
operation.

Another approach to this problem would be that we change all the stored
procedures to include a BEGIN TRAN ,COMMIT TRAN and ROLLBACK TRAN statements
and include an extra parameter in the stored procedures like isCommitTrue ( a
boolean variable ) and depending on the value of that parameter isCommitTrue
like 1 or 0 , either commit the transaction or rollback the transaction.

Just curious what the recommened strategy for restoring test data is to
start anew!

Please let me know if there is any other better approach for this.

Hi

I Since you are using SQL Server 2005, I would recommend Database
Snapshot. It is very quick to recovery from it; once you have your base
database created to the point where you can start testing, a database
snapshot doesn't take up much space on the disk. And you can revert back to
a SINGLE snapshot when needed.

I often use that for training databases; we got the database up and going
to point where we can train users on it. So take a snapshot; let first batch
of users train on it. And revert back. Doesn't matter the option use there
will be IO that can't be avoided.

DROP/CREATE DB From backup
- You don't have to drop it to restore it; just use RESTORE WITH OVERWRITE.

DELETE/RELOAD with .SQL Scripts
- Doable but I find it generally very slow to delete everything then reload
it. Because now SQL Server has to de-allocate index, space, etc.

HISTORY TABLES for Rollback
- I think this creates more work then you need for simple rollback.

BEGIN TRAN/COMMIT/ROLLBACK TRAN
- That would be a nice idea; but I am taking a guess when you are testing
there are multiple stored procedures in play. If that is the case, you'll
have to use Microsoft Transaction Coordinator to do transaction handling
outside of SQL Server so you can rollback the entire transaction from all
changes made in one transaction. This can be very confusing (sorry not MTC
expert); talking to my Developer buddies it can get into issues.

Thanks.