I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Ways to revert a database to pre-Test state after Unit testing

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("CREATE SNAPSHOT: " + cmd.ExecuteNonQuery().ToString());
            }
            catch (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("REVERT SNAPSHOT: " + cmd.ExecuteNonQuery().ToString());
            }
            catch (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.

kick it on DotNetKicks.com
 

Print | posted on Friday, July 06, 2007 7:00 PM | Filed Under [ .Net SQL Server ]

Feedback

Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

We use a different approach:

Teardown and rebuild database for each test (it's actually pretty fast).

We're using NHibernate to build the database, only a few lines of code ...
7/9/2007 11:56 AM | Poul Foged Nielsen
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

Another way is to use Virtualization either, VMWare or Microsoft Virtual PC.
7/9/2007 12:03 PM | Sean Deasy
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

Are you a single developer or do you have many people accessing your same test database? In other words, if I take a snapshot and restore I run the risk of having a co-workers UT fail or even our build server failing - right?
7/9/2007 4:15 PM | David
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

David:
you are correct.
we are multi developer team and
we use snapshots only for auto tests during the night.

if you want multiple people running multiple tests on the same db at once,
i suggest you take a look at NDbUnit.
7/9/2007 4:23 PM | Mladen
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

VMWare Workstation 5.5's multiple snapshots work very well for us here. We virtualize all test & development machines anyway, so this is the simplest and most intuitive way to go. Create the snapshot with two clicks (2 minutes), run the tests, create more snapshots as many times as you need to, run more tests, revert to whichever snapshot you want (2 minutes or often less). Done.
7/9/2007 8:36 PM | Peter
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

i haven't dealt with testing in virtual servers so tell me something:
this VMWare revert to snapshot, is it automated or has to be done manually?

because i was looking for a solution that can be fully automated.
7/9/2007 8:44 PM | Mladen
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

MbUnit is an amazing extension of NUnit. Most of what it can do is out of scope for this discussion, but one thing it supports is a [Rollback] attribute for any unit test. This starts a transaction, not at the database layer (like a BEGIN TRAN command or an ADO.Net transaction) but at the Windows layer. I think it's using the Distributed Transaction Coordinator. At any rate, with no requirement for any code changes, every database write in your test rolls back when the test completes. Theoretically, anything else (outside the database) in the test that supports transactions would also roll back although I haven't tried it.

I have only limited experience with it but it works like a charm for simple tests; performance is good on a local box. My only complaint is that the transaction management doesn't seem to play well with [Setup] methods.
7/10/2007 8:20 PM | Dream
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

thanx. i'll look into it.
7/10/2007 8:27 PM | Mladen
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

The 'catch' with the mbUnit [Rollback] attribute is more or less exactly what's mentioned here: works fine for 'simple' tests, but is problematic for more complex database interactions -- since its relying on the MSDTC infrastructure to do its rollback, any action within your test that also begins/ends a transaction itself (e.g. if your DAL employs transactions too and they DONT use the MSDTC for transaction support -- like using System.Transactions -- then you have a problem.

We have been using the NDbUnit framework for some time with pretty good success; despite it being written in .NET 1.x, it still works just fine in .NET 2.0.
7/13/2007 4:45 AM | Steve Bohlen
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

I seem to run into the error "Database state cannot be changed while other users are using the database 'dbname'" everytime I run this.

CreateDatabaseSnapshot("DB", "DB", "c:\DB_ss.ss")
Dim cnn As New SqlConnection("server=WS; database=DB; trusted_connection=yes;")
cnn.Open()

Dim cmd As New SqlCommand()
Try
cmd.Connection = cnn
cmd.CommandText = "select COUNT(*) FROM TABLE"
cmd.ExecuteScalar()

Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
If cnn.State = ConnectionState.Open Then cnn.Close()
End Try

RevertDatabaseFromSnapshot("DB", "DB_ss")

RevertDatabase throws the error:

Is there something I'm missing
11/22/2007 3:43 AM | MS
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

SqlConnection.ClearAllPools() solves this.
Thanks
11/22/2007 5:55 AM | MS
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

"SqlConnection.ClearAllPools() solves this"

Yes it does!! Thank you so much!!
5/15/2009 6:29 AM | Carlos
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

Yes, ClearAllPools works!! Thank you so much!!
5/15/2009 6:29 AM | Carlos
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

One way that has worked well for me in the past is to do each unit test in a transaction and roll it back when you're finished.

You'll need to have a way to ensure that no new transactions are started within the test. The app I was working on used a shared db connection and no new transactions would be started if there already was one open.
5/21/2009 4:32 AM | Thomas Johnson
Gravatar

# re: Ways to revert a database to pre-Test state after Unit testing

I found that using a database for unit tests get out of sync pretty quickly in a large corporation. Try pulling the data from xml files. Check out how on my blog.
3/3/2010 7:08 PM | Coding Cramp
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET