Tests are a pretty much a must in today's world of programming. There's even a whole Test Driven Development methodology that deals with this.

There comes a time when we have to test how our app interacts with the database. Usually there are 2 different types of unit testing;

one for the app itself and the other for the database. Each have it's own unit tests.

When testing the app we have to mock the database with Mock objects and similarly when testing a database we run our stored procedures

with known parameters (you do use stored procedures for ALL db access, right? :))

But i'm a lazy person and i don't feel like writing mock objects so i'm testing my app directly with the database.

The purpose of automated test is repeatability. Well written tests will always return the same result provided the outside factors are the same.

With database fiddling this comes under question. Our app can do all kinds of database manipulations which put it into an undesired state when tests finish.

So now the question arises: How do we revert our database bac to pre-Test state?

There are 4 ways I can think of:

1. Put a test into a transaction and roll it back when it finishes.

       I don't like this option too much, because you can't really always handle transaction like you want to in your .net test code.

2. NdbUnit

      NDbUnit is a .NET library for putting a database into a known state. It's quite usefull and worth checking out. It does require some coding but not much.

      Looks quite elegant.

3. Backup/Restore

       In SQL Server 2000 i've acctually used this a lot with success. With SQL Server 2005 not so much anymore. Why? Read on.

4. Database snapshots in SQL Server 2005

       With SQL Server 2005 I've started using this techique a lot because it's simple and it's faster than backup/restore and unlike NDbUnit i only call 2 functions

       once: before and after the test. Two lines of code and that's it.

       Only drawback of this is that Database snapshots are only supported on Enterprise and Developer editions.

       I use these two C# functions to save a db to snapshot before the test and put it back into the pre-test state after the test:

private void CreateDatabaseSnapshot(string databaseName, string databaseLogicalName, string snapshotPath)
{
    using (SqlConnection cnn = new SqlConnection("server=MyServer; database=master; trusted_connection=yes;"))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            try
            {
                cmd.Connection = cnn;
                cmd.CommandTimeout = 1000;
                string snapshotName = System.IO.Path.GetFileNameWithoutExtension(snapshotPath);
                cmd.CommandText = "CREATE DATABASE " + snapshotName + " ON ( NAME = " + databaseLogicalName + ", FILENAME = '" + snapshotPath + "' ) AS SNAPSHOT OF " + databaseName + ";";
            cnn.Open();
            Console.WriteLine(<span class="str">&#34;CREATE SNAPSHOT: &#34;</span> + cmd.ExecuteNonQuery().ToString());
        }
        <span class="kwrd">catch</span> (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
    cnn.Close();
}

}
CreateDatabaseSnapshot("testdb", "testdb", @"c:\testdb_ss.ss");
private void RevertDatabaseFromSnapshot(string databaseName, string snapshotName)
{
using (SqlConnection cnn = new SqlConnection("server=MyServer; database=master; trusted_connection=yes;"))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = cnn;
cmd.CommandTimeout = 1000;
cmd.CommandText = "RESTORE DATABASE " + databaseName + " FROM DATABASE_SNAPSHOT = '" + snapshotName + "'; DROP DATABASE " + snapshotName + ";";
            cnn.Open();
            Console.WriteLine(<span class="str">&#34;REVERT SNAPSHOT: &#34;</span> + cmd.ExecuteNonQuery().ToString());
        }
        <span class="kwrd">catch</span> (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

}
RevertDatabaseFromSnapshot("testdb", "testdb_ss");

Choosing the one that suits you is up to you, but remember: Testing is important.