分离本地数据库.mdf，复制并附加新文件 [英] Detach local database .mdf, copy, attach the new file

问题描述

我试图分离本地数据库.mdf将其复制到另一个文件夹中，并在启动时附加新文件，并在关闭时复制到旧文件夹中.

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 :

该进程无法访问文件'C:\ ProgramData \ MyData \ db1.mdf'，因为该文件正在被另一个进程使用.

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

那是我的代码:

    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 ?

谢谢

推荐答案

您需要切换到master并使目标数据库脱机

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

警告:使用时需要您自担风险(例如，您可以使用WITH ROLLBACK IMMEDIATE吗?)

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");

sp_detach_db的第二个参数只是避免了统计信息更新(会更快)

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

您现在可以从其原始位置安全地移动mdf和ldf文件

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

假设您的数据库已经脱机并且您已将db1.mdf文件移至D:\Whatever，我想您可以执行此操作(请注意，我没有对其进行测试)

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");

这篇关于分离本地数据库.mdf，复制并附加新文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！