Creates a database snapshot and then restores the database when it's disposed.

DatabaseSnapshot.cs

public class DatabaseSnapshot: IDisposable
{
    private readonly SqlConnection connection;
    private readonly string database;
    private bool disposed;

    public DatabaseSnapshot()
        : this(NHibernateSession.NHibernateSessionManager.Instance.GetStatelessSession().Connection as SqlConnection)
    {}

    public DatabaseSnapshot(SqlConnection connection)
        : this(connection, connection.Database)
    {}

    public DatabaseSnapshot(SqlConnection connection, string database)
    {
        this.connection = connection;
        this.database = database;
        CreateSnapshot(connection, database, DatabaseFileNames(connection, database));
    }

    private static string SnapshotName(string databaseName)
    {
        return string.Format("Backup_before_test_of_{0}", databaseName);
    }

    private static IEnumerable<string> DatabaseFileNames(SqlConnection connection, string databaseName)
    {
        var cmdText = string.Format("select name from sys.master_files where database_id=db_id('{0}') and type=0",
                                    databaseName);
        using (var command = new SqlCommand(cmdText, connection))
        using (var reader = command.ExecuteReader())
            while (reader.Read())
                yield return reader.GetString(0);
    }

    private static void CreateSnapshot(SqlConnection connection, string database, IEnumerable<string> files)
    {
        var tempPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData),
                                    "EIS");
        Directory.CreateDirectory(tempPath);

        var fileSpecs = from file in files
                        let snapshotFilename = SnapshotFilename(tempPath, file)
                        select string.Format("Name=[{0}], Filename='{1}'", file, snapshotFilename);
        var commandText = string.Format(@"if exists (select * from sys.databases where name='{0}')
                                                drop database [{0}]

                                           create database [{0}] on ({1}) as snapshot of [{2}]",
                                        SnapshotName(database), string.Join(",", fileSpecs), database);

        using (var command = new SqlCommand(commandText, connection))
            command.ExecuteNonQuery();
    }

    private static string SnapshotFilename(string path, string file)
    {
        var fileName = string.Format("{0}_{1}.ss", file, Guid.NewGuid().ToString().Substring(0, 8));
        return Path.Combine(path, fileName);
    }

    private static void RestoreSnapshot(SqlConnection connection, string database)
    {
        var commandText = string.Format(@"use master
                                        alter database [{0}] set single_user with rollback immediate 
                                        alter database [{0}] set multi_user
                                        restore database [{0}] from database_snapshot='{1}'
                                        drop database [{1}]",
                                        database, SnapshotName(database));

        using (var command = new SqlCommand(commandText, connection))
            command.ExecuteNonQuery();
    }

    public void Dispose()
    {
        if (disposed)
            return;

        try
        {
            RestoreSnapshot(connection, database);
            disposed = true;
        }
        // ReSharper disable EmptyGeneralCatchClause
        catch (Exception) {}
        // ReSharper restore EmptyGeneralCatchClause
    }
}