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!

If you do not want to perform backup/restore, then using transactions
will be probably best. Here is one article with details on the topic:
http://msdn.microsoft.com/en-us/magazine/cc163772.aspx

Also, you can use some frameworks like NDbUnit that return the database
to consistent state:
http://sourceforge.net/projects/ndbunit/
http://www.codeproject.com/KB/tips/UnitTestUtility.aspx

Hi Plamen Ratchev,

If I am taking the transactions approach to maintain constant data (i.e each
unit test will open a transaction and abort the same at the end of test),
then I need to take care of nested transaction. So I have the following
question. If we have a 2 or more SQL transactions within (inside) a SQL
transaction  , then when I commit in one of the internal SQL transactions and
then rollback on the external C# transaction , will the internal commit on
the SQL transaction be rolled back . i.e.

Begin C#_Transaction
(
Begin SQLTransaction1
// Do some insert/delete/update into some sql table
Commit SQLTransation1

Begin SQLTransaction2
// do some data manipulation
Commit SQLTransaction2
)
Rollback C#_Transaction

Now when I rollback the C#_Transaction, will it rollback the commits that
have taken place inside the SQLTransaction1 and SQLTransaction2.

Thanks,


Show quoteHide quote

Yes, the outer transaction will roll back all inner transactions. By
definition the ROLLBACK statement backs out all modifications made in
the transaction by returning the data to the state it was in at the
start of the transaction. Try this:

CREATE TABLE Foo (
  keycol INT PRIMARY KEY,
  datacol CHAR(1));

INSERT INTO Foo VALUES(1, 'a');

GO

BEGIN TRAN C#_Transaction;

SELECT keycol, datacol
FROM Foo;

BEGIN TRAN SQLTransaction1;

UPDATE Foo
SET datacol = 'b'
WHERE keycol = 1;

SELECT keycol, datacol
FROM Foo;

COMMIT TRAN SQLTransation1;

SELECT keycol, datacol
FROM Foo;

BEGIN TRAN SQLTransaction2;

INSERT INTO Foo VALUES(2, 'd');

COMMIT TRAN SQLTransaction2;

SELECT keycol, datacol
FROM Foo;

ROLLBACK TRAN C#_Transaction;

SELECT keycol, datacol
FROM Foo;

GO

DROP TABLE Foo;

Query with dinamic columns
Restoring single filegroup
Migrating SQL2000 Databases to SQL2005
SSMS Sometimes shows keys - sometimes not
help on tempdb log full
Change autogrowth for a log file SQL 2005
Database file and Transaction log file
SQL native Client for client failover
Maintenance Plans
Error: 18456, Severity: 14, State: 10.