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.

or
Another option would be to place the database initial setup data in external
files like .csv files , then after one round of testing, run a delete query
to delete data from the database tables and the load the data from the
database from the external .csv files, so that the database gets ready for
the 2nd round of testing.

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

I believe ,this testing is mainly targeted towards Development Phase and for
a Pre-Production test scenario, the best approach would be to drop and
restore the proper version of the database . Please let me know your views on
the same.

Thanks in Advance.