Detach local database .mdf, copy, attach the new file

Question

I tried to detach my local database .mdf copy it in another folder and attach the new file at launch and copy to the older folder when closing.

It seems to works at launch but i have an error when the form closing :

The process cannot access the file 'C:\ProgramData\MyData\db1.mdf' because it is being used by another process.

That's my code :

    public Form()
    {
        InitializeComponent();
        DetachDatabase();
        CopyDb();
        AttachDatabase();
        AppDomain.CurrentDomain.SetData("DataDirectory", Data.MyNewFolder);    
    }


    public static bool DetachDatabase()
    {
        try
        {

            string connectionString = String.Format(@"Data Source=(LocalDB)\v11.0;Initial Catalog=master;Integrated Security=True");
            using (var cn = new SqlConnection(connectionString))
            {
                cn.Open();
                SqlCommand cmd = cn.CreateCommand();
                cmd.CommandText = String.Format("exec sp_detach_db '{0}'", "db1");
                cmd.ExecuteNonQuery();
                cmd.CommandText = String.Format("exec sp_detach_db '{0}'", "db2");
                cmd.ExecuteNonQuery();
                return true;
            } 
        }
        catch
        {
            return false;
        }
    }

    public static bool AttachDatabase()
    {
        try
        {
            string connectionString = String.Format(@"Data Source=(LocalDB)\v11.0;Initial Catalog=master;Integrated Security=True");
            using (var cn = new SqlConnection(connectionString))
            {
                cn.Open();
                SqlCommand cmd = cn.CreateCommand();
                cmd.CommandText = String.Format("exec sys.sp_attach_db    db1,    'db1.mdf'");
                cmd.CommandText = String.Format("exec sys.sp_attach_db    db2,    'db2.mdf'");
                cmd.ExecuteNonQuery();
                return true;
            }
        }
        catch
        {
            return false;
        }
    }

    private void Frm_FormClosing(object sender, FormClosingEventArgs e)
    {
        LocalDB.DetachDatabase();
        CopyDb();
        LocalDB.AttachDatabase();
    }

what is the good way for do it ?

Thanks

Answer 1

You need to switch to master and put the target database offline

WARNING: Use at your own risk (e.g. can you use WITH ROLLBACK IMMEDIATE?)

var commandText = string.Format(@"
    USE MASTER;
    ALTER DATABASE {0} SET OFFLINE WITH ROLLBACK IMMEDIATE;
    EXEC sp_detach_db '{0}', 'true';", "db1");

The second parameter to sp_detach_db just avoids statistics update (would be faster)

You can now safely move the mdf and ldf files from their original location

Assuming your database is already offline and you have moved your db1.mdf file into D:\Whatever, I think you can do this ( I didn't test it, beware )

var commandText = string.Format(@"
    USE MASTER;
    ALTER DATABASE {0}
      MODIFY FILE (
        NAME = '{0}',
        FILENAME = 'D:\Wherever\{0}.mdf');
    ALTER DATABASE {0} SET ONLINE;", "db1");