Database snapshots!

Save this script and name it "create_db_snapshot.sql"

/* Create a database snapshot */USE master;CREATE DATABASE Your_Database_Snapshot ON(     NAME = Your_Database,     FILENAME = 'C:\Snapshots\Your_Database_Snapshot.ss' )AS SNAPSHOT OF Your_Database;GO

Also, save this script and name it "restore_db_from_snapshot.sql"

USE master;RESTORE DATABASE Your_Database from DATABASE_SNAPSHOT = 'Your_Database_Snapshot';GO

Example:

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using Microsoft.SqlServer.Management.Smo;using Microsoft.SqlServer.Management.Common;using System.IO;using System.Data.SqlClient;[SetUp]public void TestSetUp(){    string sqlConnectionString = @"server=test.database.com;uid=your_db_username;pwd=your_db_password;database=Your_Database;";    string script = File.ReadAllText(@"~/create_db_snapshot.sql");    SqlConnection conn = new SqlConnection(sqlConnectionString);    Server server = new Server(new ServerConnection(conn));    server.ConnectionContext.ExecuteNonQuery(script);}[TearDown]public void TearDown(){    string sqlConnectionString = @"server=test.database.com;uid=your_db_username;pwd=your_db_password;database=Your_Database;";    string script = File.ReadAllText(@"~/restore_db_from_snapshot.sql");    SqlConnection conn = new SqlConnection(sqlConnectionString);    Server server = new Server(new ServerConnection(conn));    server.ConnectionContext.ExecuteNonQuery(script);}

Snapshot documentation: https://msdn.microsoft.com/en-us/library/ms175158.aspx

Code credit for executing .sql file: https://stackoverflow.com/a/1728859/3038677

You might also need to run this script prior to executing restore_db_from_snapshot.sql...

/* Kill all current connections to Your_Database */use master;DECLARE @kill varchar(8000) = '';SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'FROM master..sysprocesses WHERE dbid = db_id('Your_Database')

I use database snapshots for Selenium testing of my web application. In the Setup method I roll back the database to a snapshot taken just after restoring the database from production. This guarantees the database is in the same state for each test run, but still allows you to test the UI. I have created several temporary stored procedures, for example #usp_restore_snapshot, to avoid littering the database and the unit test with SQL code just used for testing..

One way is to use https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx and it will rollback everything back automatically, even on test failure, because there is no commit statement.

like:

[Test]public void SomeTest() {    using (TransactionScope scope = new TransactionScope())    {        // here comes your test    }}

A "better" and "safer" way is the way you told in your question doing it via TearDown:

[TestFixture]public class YourFixture{    private TransactionScope scope;    [SetUp]    public void TestSetUp()    {        scope = new TransactionScope();    }    [TearDown]    public void TearDown()    {        scope.Dispose();    }    [Test]    public void SomeTest()    {        // here comes your test     }}

Why? Because NUnit is your garanty that TearDown will be called.