Respawn vs SQL Server Snapshots

One of the newer features of SQL Server is the ability to snapshot a database at a point in time, and restore it. A commenter on the Respawn announcement asked if I had looked at this feature for resetting a test database to a known point.

I hadn't, and this feature offers a few perks over Respawn. Respawn is an intelligent table deleter, instead of giving you an actual snapshot point-in-time. Personally, I've not needed snapshots as points-in-time (they might even be a test smell). Either my data is static and unchanging, or transactional and part of my test.

In any case, I had originally pitted Respawn against many test database techniques, including:

• Transaction rollbacks (problematic)
• Drop/recreate
• Restore backup
• Drop constraints/truncate/enable constraints

All of these were slower than intelligently deleting tables. Of course, the smartest way would be to have tests that don't need tables cleared out - but that's another story.

The general idea behind snapshots is that I create a blank version of a test database and a snapshot at the beginning of an entire test run:

// This is called in a static initializer
private static void CreateSnapshot()
{
    var checkpoint = new Checkpoint(); //Respawn
    checkpoint.Reset(TestConnectionString).GetAwaiter().GetResult();

    using (var conn = new SqlConnection(MasterConnectionString))
    {
        conn.Open();
        var snapshotDbName = TestDbName + "-Snapshot";
        // Delete the snapshot DB if exists
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = $"DROP DATABASE IF EXISTS [{snapshotDbName}]";
            cmd.ExecuteNonQuery();
        }

        // Create a snapshot using the blank test DB
        using (var cmd = conn.CreateCommand())
        {
            var filename = Path.Combine(Environment.CurrentDirectory, $"{snapshotDbName}.ss");
            cmd.CommandText = $@"CREATE DATABASE [{snapshotDbName}] 
ON (Name = [{TestDbName}], Filename='{filename}')
AS SNAPSHOT OF [{TestDbName}]";
            cmd.ExecuteNonQuery();
        }
    }
}

Then, as part of a fixture setup, I'd reset the database to my known checkpoint:

public static async Task ResetCheckpoint()
{
    using (var conn = new SqlConnection(MasterConnectionString))
    {
        await conn.OpenAsync();
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = $@"
ALTER DATABASE [{TestDbName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [{TestDbName}] FROM DATABASE_SNAPSHOT = '{TestDbName}-Snapshot';
ALTER DATABASE [{TestDbName}] SET MULTI_USER;";
            await cmd.ExecuteNonQueryAsync();
        }
    }
}

I tried this against a small test project and an actual client project. The small test project had 40 tests, and the client one 1100. The results for 1100 tests:

• Respawn - 5.8 seconds
• Snapshots - 470 seconds

Couple of orders of magnitude off, but this more or less matches the times for doing it via DB backups. Still worth testing out in your codebase, though. When we're executing a suite of integration tests, even a small improvement in a single test adds up when we have hundreds or thousands of them.