For testing purposes it is possible to copy a new localdb database to your output directory and start using this localdb database. For more information see my previous post: https://www.roelvanlisdonk.nl/?p=2607

It is possible that a previous tests run did not cleanup it’s copy of the localdb database. Now if you want to attach an new localdb database with the same name, the attach will fail. There are several options you have to fix this error. I decided to just "re-use" the existing database, just changing the *.mdf and *.ldf file location.

 

The following code can help you do just that:

using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;

namespace Test.Research
{
    /// <summary>
    /// Tests in this class are only used to analyse and research code.
    /// It is not intended to contain real integration tests.
    /// </summary>
    [TestClass]
    public class RliResearchTester
    {
        
        /// <summary>
        /// Changes the *.mdf and *.ldf file location for the given LocalDb database, when it exists.
        /// </summary>
        /// <remarks>
        /// It expects the mdf file name to be in the format [DatabaseName.mdf].
        /// It expects the ldf file name to be in the format [Databasename_log.ldf].
        /// It expects the old mdf file name to be equal to the new mdf file name (only the file location will change).
        /// It expects the old ldf file name to be equal to the new ldf file name (only the file location will change).
        /// </remarks>
        [TestMethod]
        public void ChangeMdfAndLdfLocationForExistingLocalDb()
        {
            string dataSource = @"(localdb)\V11.0";
            string databaseName = "MyReserachDb";
            string folderContainingNewDatabaseFiles = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string oldMdfFilePath = GetMdfFilePath(dataSource, databaseName);
            if (!string.IsNullOrWhiteSpace(oldMdfFilePath))
            {
                ModifyDatabaseFilePaths(dataSource, databaseName, folderContainingNewDatabaseFiles);
            }
        }
        /// <summary>
        /// Gets the full physical mdf filepath, based on the supplied data soruce (Servername\Instancename) and database name.
        /// </summary>
        /// <param name="dataSource">SQL Server instance in the format (ServerName\InstanceName)</param>
        /// <param name="databaseName">Name of the database.</param>
        /// <returns>
        /// Databse found:     Returns the full physical mdf filepath.
        /// Databse not found: Returns null.
        /// </returns>
        /// <remarks>
        /// Uses windows authentication to connect.
        /// This function does not take into account, the fact that a mdf file name can be different, then the [DatabaseName.mdf].
        /// </remarks>
        public string GetMdfFilePath(string dataSource, string databaseName)
        {
            string result = null;
            string mdfName = databaseName;
            using (var connection = new SqlConnection(string.Format(@"Data Source=""{0}"";Initial Catalog=master;Integrated Security=True;", dataSource)))
            using (var command = new SqlCommand("select top 1 physical_name from sys.master_files where name = @MdfName", connection))
            {
                command.Parameters.Add(new SqlParameter("MdfName", mdfName));

                connection.Open();
                result = command.ExecuteScalar() as string;
            }
            return result;
        }
        /// <summary>
        /// Modifies the *.mdf and *.ldf database file locations of an existing online database.
        /// </summary>
        /// <param name="dataSource">SQL Server instance in the format (ServerName\InstanceName)</param>
        /// <param name="databaseName">Name of the database.</param>
        /// <param name="folderContainingNewDatabaseFiles">The folder containing new database files.</param>
        /// <remarks>
        /// - Uses windows authentication to connect.
        /// - Drops all existing database connections, by using [with rollback immediate] on the [set offline].
        /// - Forces the database to use the new files, by using [set offline] and [set online].
        /// - It expects the mdf file name to be in the format [DatabaseName.mdf].
        /// - It expects the ldf file name to be in the format [Databasename_log.ldf].
        /// - It expects the old mdf file name to be equal to the new mdf file name (only the file location will change).
        /// - It expects the old ldf file name to be equal to the new ldf file name (only the file location will change).
        /// </remarks>
        public void ModifyDatabaseFilePaths(string dataSource, string databaseName, string folderContainingNewDatabaseFiles)
        {
            string mdfName = databaseName;
            string ldfName = string.Format("{0}_log", databaseName);
            string newMdfFileLocation = Path.Combine(folderContainingNewDatabaseFiles, string.Format("{0}.mdf", mdfName));
            string newLdfFileLocation = Path.Combine(folderContainingNewDatabaseFiles, string.Format("{0}.ldf", ldfName));
            string query = string.Format(@"
                alter database {0} modify file (name = {1}, filename = '{3}');
                alter database {0} modify file (name = {2}, filename = '{4}');
                alter database {0} set offline with rollback immediate;
                alter database {0} set online;", 
                new string[] { databaseName, mdfName, ldfName, newMdfFileLocation, newLdfFileLocation });
            using (var connection = new SqlConnection(string.Format(@"Data Source=""{0}"";Initial Catalog=master;Integrated Security=True;", dataSource)))
            using (var command = new SqlCommand(query, connection))
            {
                connection.Open();
                command.ExecuteNonQuery();
            }
        }
    }
}

One Comment

  1. Pingback: Using SQL Server 2012 LocalDb in VS11 and VS2010 for testing.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.