Recover database between unit tests: Database is still in use

Question

We got some unit tests that are working with SQL Server databases. To make at least every test fixture unique and independent to other test fixtures I try to recover the database before every test fixture starts. Every test opens and closes the connection and database in its routine.

To restore the database before the first test fixture works quite well, but to restore the database before the second test fixture (after the connection and database has been opened and closed) does not.

I capsuled and isolated the problem. Here are the two sample tests (NewUnitTest1 will be executed first):

using NUnit.Framework;
using System.Data;
using System.Data.SqlClient;

    [TestFixture]
    class UnitTest1 : BaseTest
    {
        [Test]
        public void NewUnitTest1()
        {
            string conString = ConnectionStringHelper.GetConnectionString(SCADADatabases.ConfigurationDatabase); // Helper method to optain connection string, is correct
            using (SqlConnection dbConn = new SqlConnection(conString))
            {
                SqlCommand cmd = dbConn.CreateCommand();
                cmd.CommandText = "SELECT * FROM TB_PV";
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
            } // Dispose should close connection and database ...
            Assert.IsTrue(true);
        }
    }

    [TestFixture]
    class UnitTest2 : BaseTest
    {
        [Test]
        public void NewUnitTest2()
        {
            Assert.IsTrue(true);
        }
    }

The base test class will do the recovery job before every test fixture:

using My.Core.Helper;
using My.Core.UnitTest.Properties;
using My.DBRestore.Core;
using My.Domain;
using Microsoft.SqlServer.Management.Smo;
using NUnit.Framework;
using System;
using System.Data;
using System.IO;

    /// <summary>
    /// Base test class any test class can inherit from. 
    /// </summary>
    public abstract partial class BaseTest
    {
        /// <summary>
        /// Executes before all tests of this class start. 
        /// </summary>
        [TestFixtureSetUp]
        public virtual void FixtureSetUp()
        {
            Console.WriteLine("Recover database ... ");
            restoreDatabase("MYDATABASE", @"D:\MYBACKUP.BAK", "");
            Console.WriteLine("Run tests in " + this.GetType() + " ...");
        }

        private void restoreDatabase(string destinationDatabase, string backupFile, string dbPath)
        {
            Microsoft.SqlServer.Management.Smo.Server sqlServer = new Microsoft.SqlServer.Management.Smo.Server(Properties.Settings.Default.SQLInstance);

            Microsoft.SqlServer.Management.Smo.Restore restore = new Microsoft.SqlServer.Management.Smo.Restore();
            restore.Action = Microsoft.SqlServer.Management.Smo.RestoreActionType.Database;
            restore.Devices.Add(new Microsoft.SqlServer.Management.Smo.BackupDeviceItem(backupFile, Microsoft.SqlServer.Management.Smo.DeviceType.File));

            System.Data.DataTable dt = restore.ReadBackupHeader(sqlServer);
            restore.FileNumber = Convert.ToInt32(dt.Rows[dt.Rows.Count - 1]["Position"]);

            dt = restore.ReadFileList(sqlServer);
            int indexMdf = dt.Rows.Count - 2;
            int indexLdf = dt.Rows.Count - 1;
            Microsoft.SqlServer.Management.Smo.RelocateFile dataFile = new Microsoft.SqlServer.Management.Smo.RelocateFile();
            string mdf = dt.Rows[indexMdf][1].ToString();
            dataFile.LogicalFileName = dt.Rows[indexMdf][0].ToString();
            dataFile.PhysicalFileName = Path.Combine(dbPath, destinationDatabase + Path.GetExtension(mdf));


            Microsoft.SqlServer.Management.Smo.RelocateFile logFile = new Microsoft.SqlServer.Management.Smo.RelocateFile();
            string ldf = dt.Rows[indexLdf][1].ToString();
            logFile.LogicalFileName = dt.Rows[indexLdf][0].ToString();
            logFile.PhysicalFileName = Path.Combine(dbPath, destinationDatabase + Path.GetExtension(ldf));

            restore.RelocateFiles.Add(dataFile);
            restore.RelocateFiles.Add(logFile);

            restore.Database = destinationDatabase;
            restore.ReplaceDatabase = true;
            restore.SqlRestore(sqlServer); // <- this is where the FailedOperationException is thrown on the second execution
        }
}

As stated, the restoring works quite well on the first time. On the second time the FailedOperationException claims: The exclusive access to the database is not possible, because the database is currently in use. RESTORE DATABASE will be stopped with errors. (Manually translated by me)

We are using the latest NUnit 2 release (2.6.4). Why the database is still in use and how can I close it, correctly?

Answer 1

You need to kill all processes connected to the database before the restore:

sqlServer.KillAllProcesses(destinationDatabase)

Check the documentation for more details.