DROP DATABASE statement cannot be used inside a user transaction

Not really sure if this question belongs here, but I hope someone could help me out.

I've made integration tests going all the way down to the database (using mssql localDB). I want each test to run independently with it's own data - I want to reseed the database with my fake data before each test is running. I tried to implement it with transactions without success. Here is how I tried to pull it off:

public class TestDbInitializer : DropCreateAlways()
{
    public static List Items;

    public override Seed(DbContext context)
    {
        Items = new List();

        // Adding items
        // .. 

        Items.ForEach(x => context.Add(x));

        context.SaveChanges();
    }
}

public class BaseTransactionsTests
{
    private TransactionScope _scope

    [TestInitialize]
    public void Initialize()
    {
        _scope = new TransactionScope();
    }

    [TestCleanup]
    public void Cleanup()
    {
        _scope.Dispose();
    }
}

[TestClass]
public class IntegrationTests : BaseTransactionsTests

private IDependenciesContainer _container;

public static void AssemblyInit(TestContext context)
{
    Database.SetInitializer(new TestDbInitializer());

    _container = new DependenciesContainer();

    // Registers all my application's dependencies
    _container.RegisterAll();
}

[TestInitialize]
public void Initialize()
{
    using (var context = new MyContext("TestsDatabase"))
    {
        context.Initialize(true);
    }
}

[TestMethod]
public void TestAddItem()
{
    var controller = _container.Resolve();

    var result = controller.AddItem(new Item({Name = "Test"}))

    var goodResult = result as OkNegotiatedResult();

    if (result == null)
        Assert.Fail("Bad result")

    using (var context = new MyContext("TestsDatabase"))
    {
        Assert.AreEqual(context.Items.Count, TestDbInitializer.Items.Count + 1)
    }
}

I use my dependency injector in my tests, registering all dependencies once (AssemblyInitialize).

I created a DB in

Create a separate initializer that drops the database if it exists, then creates the database outside of any transaction handling code. If this fails for any reason, you could (probably) just fail the test right then and investigate why it failed.

From MSDN:


The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

If you want, you could do this for each individual test:

• DROP DATABASE if it exists

• CREATE DATABASE

•  Create necessary DDL

•  Run DML unit test

• DROP DATABASE

However, that would be overkill. I'd simply create the database at the start of all tests, and drop it at the end. Each unit test could be wrapped in a transaction which is either rolled-back or committed, depending on the requirements of further tests.

I looked for a resource that explains why CREATE DATABASE cannot be used inside a transaction, but I couldn't find one. Kenneth Fisher provided the following TechNet link, which shows all commands that won't work in a transaction:

Transact-SQL Statements Allowed in Transactions

Our guess is that a lot of them aren't allowed because they affect the file system, which is not transactional.

StackExchange Database Administrators Q#134429, answer score: 6