Restoring state of database after running a Unit test

Sorry if this is a newbie question.
My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.
I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.
I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.
2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.
Unfortunately rolling back is not an option for me because the
transaction was commited by that different third party application.
Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?
Thanks.Hi
Your best option is to restore a backup, you only need one known state
backup and you can restore consistently (use a script rather than the GUI as
this can be automated using test tools). If you don't want to do that have
copies of the MDF and LDF files and detach/copy/re-attach instead.
Unless you are testing performance and require a large amount of data in the
database, then using a smaller amount will back restoring it quicker.
John
"sprash" <sprash25@.gmail.com> wrote in message
news:9bc7966a-638e-4ff3-9b4e-6e5972c32ec7@.u10g2000prn.googlegroups.com...
> Sorry if this is a newbie question.
> My application sends data (via TCP/IP sockets) to a 3rd party
> application which dispenses the data to various tables in a SQL Server
> 2005 database based on its own business logic.
> I am in the process of writing Unit tests for my application that
> mimics the sending of this data. However on test Teardown I would like
> to delete this test data so that next time I run the tests, the
> database is in a known state.
> I can think of a few options, none of which sound ideal to me:
> 1. Run the profiler on the database and note all the tables it touches
> and delete the those rows in the reverse order in which it was
> inserted. This is less than ideal because insertion is happening via
> stored complex procedures and it would be an arduous task going
> through the code of all the stored procs and coming up with a list
> such the referential constraints are not violated on deletion.
> 2. Backup and Restore the database.This is quite a time consuming
> operation and would be a huge bottleneck to do that everytime a test
> suite is run.
> Unfortunately rolling back is not an option for me because the
> transaction was commited by that different third party application.
> Is there some other mechanism by which I can tell SQL Server: "Note
> the state of the database now, and essentially rollback to it when I
> tell you so (in test teardown)"?
> Thanks.