用c#连接SQL Server数据库

本文关键字：Server 数据库 SQL 连接 | 更新日期: 2023-09-27 18:05:09

我正在用c#开发一个项目。我想分离，然后连接SQL Server数据库从c#代码使用SMO。我可以分离数据库，但是当我想要附加它时，出现错误:

无法打开物理文件"C:'Program Files'Microsoft SQL Server'MSSQL12.MSSQLSERVER'MSSQL'DATA'ghale.mdf"。操作系统错误5:"5(Access is denied.)".

代码:

OpenFileDialog f = new OpenFileDialog();
f.Title = "sqlbakcup";
f.FileName = "";
f.Filter = @"SQL Backup files (*.BAK) |*.BAK|All files(*.*) |*.*";
if (f.ShowDialog() == DialogResult.OK)
{
    SqlConnection sqlconn = new SqlConnection(@"Data Source=.;Initial Catalog=master;Integrated Security=True");
    ServerConnection serverconn = new ServerConnection(sqlconn);
    Server s = new Server(serverconn);
    s.DetachDatabase("ghale", false);
    //MessageBox.Show("ok");
    SqlConnection sqlconn1 = new SqlConnection(@"Data Source=.;Initial Catalog=mydb2;Integrated Security=True");
    SqlCommand sqlcmd = new SqlCommand("ALTER DATABASE mydb2  SET SINGLE_USER with ROLLBACK IMMEDIATE " + "use master  " + "RESTORE DATABASE [mydb2] FROM  DISK = @n WITH REPLACE ", sqlconn1);
    sqlcmd.Parameters.AddWithValue("@n", f.FileName);
    sqlconn1.Open();
    sqlcmd.ExecuteNonQuery();
    sqlconn1.Close();
    SqlConnection sqlconn2 = new SqlConnection(@"Data Source=.;Initial Catalog=master;Integrated Security=True");
    ServerConnection serverconn2 = new ServerConnection(sqlconn2);
    Server s1 = new Server(serverconn2);
    s1.AttachDatabase("ghale", new StringCollection {
            @"C:'Program Files'Microsoft SQL Server'MSSQL12.MSSQLSERVER'MSSQL'DATA'ghale.mdf",
            @"C:'Program Files'Microsoft SQL Server'MSSQL12.MSSQLSERVER'MSSQL'DATA'ghale_log.ldf" }, AttachOptions.None);
    //MessageBox.Show("ok");
    MessageBox.Show("OK");
}

用c#连接SQL Server数据库

几点观察:

  1. 变量sqlconn2, serverconn2和s1是不必要的，因为您可以重用变量s来重新附加ghale数据库
  2. 如果我注释掉恢复备份文件到mydb2的中间部分，重新附加ghale数据库工作
  3. 我怀疑您正在将ghale备份文件恢复到数据库mydb2，如果是这样，如果您查看mydb2的数据库属性|文件，您将看到它正在使用文件ghale.mdf和ghale_log.ldf。因此，您不能重新附加ghale.mdf，因为该文件现在正在被数据库mydb2使用，因此出现Access Denied错误消息。

:我认为下面的代码是你想要完成的: 

OpenFileDialog f = new OpenFileDialog();
f.Title = "sqlbakcup";
f.FileName = "";
f.Filter = @"SQL Backup files (*.BAK) |*.BAK|All files(*.*) |*.*";
if (f.ShowDialog() == DialogResult.OK)
{
    using (SqlConnection sqlconn = new SqlConnection(@"Data Source=.;Initial Catalog=master;Integrated Security=True"))
    {
        ServerConnection serverconn = new ServerConnection(sqlconn);
        Server s = new Server(serverconn);
        s.DetachDatabase("ghale", false);
        SqlConnection sqlconn1 = new SqlConnection(@"Data Source=.;Initial Catalog=mydb2;Integrated Security=True");
        SqlCommand sqlcmd = new SqlCommand(
            "ALTER DATABASE [mydb2] SET SINGLE_USER with ROLLBACK IMMEDIATE use master " 
            + @"RESTORE DATABASE [mydb2] FROM DISK = @n WITH REPLACE, "
            + @"MOVE 'ghale'     TO 'C:'Program Files'Microsoft SQL Server'MSSQL12.MSSQLSERVER'MSSQL'Data'mydb2.mdf', "
            + @"MOVE 'ghale_log' TO 'C:'Program Files'Microsoft SQL Server'MSSQL12.MSSQLSERVER'MSSQL'Data'mydb2_log.ldf' "
            + @"ALTER DATABASE [mydb2] MODIFY FILE (NAME=N'ghale', NEWNAME=N'mydb2')"
            + @"ALTER DATABASE [mydb2] MODIFY FILE (NAME=N'ghale_log', NEWNAME=N'mydb2_log')",
        sqlconn1);
        sqlcmd.Parameters.AddWithValue("@n", f.FileName);
        sqlconn1.Open();
        sqlcmd.ExecuteNonQuery();
        sqlconn1.Close();
        s.AttachDatabase("ghale",
                        new StringCollection {
        @"C:'Program Files'Microsoft SQL Server'MSSQL12.MSSQLSERVER'MSSQL'DATA'ghale.mdf",
        @"C:'Program Files'Microsoft SQL Server'MSSQL12.MSSQLSERVER'MSSQL'DATA'ghale_log.ldf" },
                        AttachOptions.None);
    }
}

我已经更新了mydb2数据库恢复sql，以便为数据和日志使用不同的物理文件，以及重命名逻辑名称。

试试这个…

首先你应该找到你的sqlserver服务帐户，你可以在任务管理器中看到，当你按ctrl+alt+delete同时;然后，你必须给服务帐户"C:'Murach'SQL Server 2008'Databases"的读/写权限

相关文章：