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?

Actually you should refactor your code (abstraction) in such way that you do NOT need the database to test your code. Now you are not testing only the "unit under test" but database stuff, too. — Mikko Viitala, Jan 05 '16 at 19:11
This is related: [Error - Exclusive access could not be obtained because the database is in use](http://stackoverflow.com/questions/22209499/error-exclusive-access-could-not-be-obtained-because-the-database-is-in-use/22209822#22209822) — Dave Mason, Jan 05 '16 at 20:45
@MikkoViitala Thanks for your suggestion. In our unit tests we are explicit testing insert, update and remove operations through our BLL and DAL. So the key functionality of the tested functions is to work with the database. I'm fairly confused how to test those logic that ends with SQL statements (that need to be tested, too) in an abstracted refactored way, as you suggest. — Martin Braun, Jan 06 '16 at 08:54
@DMason It might nailed down to the same problem, but the plot is different entirely. In my first idea, I was suspecting the progress "vstest.executionengine.x86.exe" has problems to close the connection until it closes itself. Maybe it could have been a bug in nunit, too. — Martin Braun, Jan 06 '16 at 08:57
Business logic should not be dependent on actual implementation of DAL. So I'd abstract DAL and use mocked implementation when testing BL. This way you can test both in isolation. — Mikko Viitala, Jan 06 '16 at 10:07
@MikkoViitala BLL is not depending on DAL, it's using it, though. You are definitely right about testing the BLL separate. Mocking implementations are new land for me, so I have no idea how this would work. At least using stubs instead of using the DAL when testing the BLL would be a better step I've to admit, but this would double the count of testing functions and people here are looking for a high valuable production rate and this is the result. — Martin Braun, Jan 06 '16 at 10:40
High productivity in the short run yes, but here we are solving a problem that could have been avoided :) Personally I prefer some ORM over databases e.g. dapper, EF. Moq is a nice and quite easy tool to create mocks on-the-fly. — Mikko Viitala, Jan 06 '16 at 10:47

score 2 · Accepted Answer · answered Jan 05 '16 at 17:34

2

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

sqlServer.KillAllProcesses(destinationDatabase)

Check the documentation for more details.

answered Jan 05 '16 at 17:34

Alex

21,273
10
61
73

Okay, this works. But why is this required. I mean, once the `SqlConnection` object calls `Dispose()` shouldn't it do this, automatically? I guess this is some sort of caching to reopen it faster again, isn't it? – Martin Braun Jan 06 '16 at 08:50
Your first calll to `restoreDatabase` database may leave the connection open. – Alex Jan 06 '16 at 09:06
Just tested it, no it's not. – Martin Braun Jan 06 '16 at 10:12
If you run your original code do you see any open sessions in the Activity Monitor in SQL Management Studio. – Alex Jan 06 '16 at 10:46

Recover database between unit tests: Database is still in use

1 Answers1