尝试分离/附加数据库 - 分离后无法访问数据库

Question

我试图做以下事情：

• 分离数据库

• 将文件复制到临时文件夹中

• 再次附加数据库

这在第一次运行时有效，但是当我尝试从同一进程第二次运行此方法时，会出现错误。我始终可以从其他客户端访问重新附加的数据库，但无法从我的应用程序中访问。

错误信息如下： “Transport level error while trying to send the request to the server.(provider: Shared Memory-Provider, error: 0 - no process at the other end of the pipe.)”，当我尝试从新附加的数据库的sys.database_files中读取数据时。

该错误来自德语翻译“Fehler auf Übertragungsebene beim Senden der Anforderung an den Server.”。它发生在“cmdGetDBFileName.ExecuteReader”之后。我仍然可以打开连接，但查询sys.database_files失败。

源代码相当长，但我认为您可以跳过获取要分离的数据库文件名的开头部分。您是否能看到我的错误或有任何想法我可以检查？

public bool DetachB2CPrepare()
        {
            _log.Debug("DetachB2CPrepare");
            SqlConnection prepareDBConnection = null;
            SqlConnection prepareMasterDBConnection = null;
            SqlDataReader readerDbFiles = null;

            bool result = true;
            try
            {
                //rc_b2c_product_prepare.mdf    
                string prepareDBPysicalFileName = "";
                //rc_b2c_product_prepare    
                string prepareDBFileName = "";
                //D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\rc_b2c_product_prepare.mdf
                string prepareDBFileNameComplete = "";

                //rc_b2c_product_prepare_1.ldf  
                string prepareTransactionLogPhysicalFileName = "";
                //rc_b2c_product_prepare_log    
                string prepareTransactionLogFileName = "";
                //D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\rc_b2c_product_prepare_1.ldf
                string prepareTransactionLogFileNameComplete = "";
                _log.DebugFormat("Try to open B2CPrepare");
                prepareDBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["B2CPrepare"].ConnectionString);
                prepareDBConnection.Open();



                //Get the file names  of DB
                SqlCommand cmdGetDBFileName = new SqlCommand("select name , physical_name, type from sys.database_files where type= 0");
                cmdGetDBFileName.Connection = prepareDBConnection;
                readerDbFiles = cmdGetDBFileName.ExecuteReader();
                if (readerDbFiles.Read())
                {
                    prepareDBFileName = (string)readerDbFiles["name"];
                    prepareDBFileNameComplete = (string)readerDbFiles["physical_name"];
                    int lastSlash = prepareDBFileNameComplete.LastIndexOf(@"\");
                    prepareDBPysicalFileName = prepareDBFileNameComplete.Substring(lastSlash + 1, prepareDBFileNameComplete.Length - lastSlash - 1);
                    readerDbFiles.Close();
                }
                 else{
                     return false;
                 }

                cmdGetDBFileName.CommandText = "select name , physical_name, type from sys.database_files where type= 1";
                readerDbFiles = cmdGetDBFileName.ExecuteReader();
                if (readerDbFiles.Read())
                {
                    prepareTransactionLogFileName = (string)readerDbFiles["name"];
                    prepareTransactionLogFileNameComplete = (string)readerDbFiles["physical_name"];
                    int lastSlash = prepareTransactionLogFileNameComplete.LastIndexOf(@"\");
                    prepareTransactionLogPhysicalFileName = prepareTransactionLogFileNameComplete.Substring(lastSlash + 1, prepareTransactionLogFileNameComplete.Length - lastSlash - 1);
                    readerDbFiles.Close();
                }
                else
                {
                    return false;
                }

                _log.DebugFormat("shrink transactionlog {0}", prepareTransactionLogFileName);

                SqlCommand cmdShrinkPrepare = new SqlCommand(string.Format(@"DBCC Shrinkfile('{0}',100) ", prepareTransactionLogFileName));
                cmdShrinkPrepare.Connection = prepareDBConnection;
                cmdShrinkPrepare.ExecuteNonQuery();

                //master auf MyProductName
                prepareMasterDBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyProductNameMaster"].ConnectionString);
                prepareMasterDBConnection.Open();

                _log.Debug("cmdOffline");

                //Datenbank verbindunge löschen 
                SqlCommand cmdOffline = new SqlCommand(@"ALTER DATABASE rc_b2c_product_prepare SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
                cmdOffline.Connection = prepareMasterDBConnection;
                cmdOffline.ExecuteNonQuery();

                _log.Debug("cmdDetach: rc_b2c_product_prepare"  );

                SqlCommand cmdDetach = new SqlCommand(@"dbo.sp_detach_db @dbname = N'rc_b2c_product_prepare',@keepfulltextindexfile = N'false'");
                cmdDetach.Connection = prepareMasterDBConnection;
                cmdDetach.ExecuteNonQuery();

                string pathForCopies = MyProductName.Backend.settings.B2CPrepareDBBackupPath;

                //copy files to temp folder
                string tempFileDB = pathForCopies + "\\" + prepareDBPysicalFileName;
                string tempFileLog = pathForCopies + "\\" + prepareTransactionLogPhysicalFileName;

                _log.DebugFormat("Copy: {0} TO: {1}", prepareDBFileNameComplete, tempFileDB);

                System.IO.File.Copy(prepareDBFileNameComplete, tempFileDB, true);

                _log.DebugFormat("Copy: {0} TO: {1}", prepareTransactionLogFileNameComplete, tempFileLog);

                System.IO.File.Copy(prepareTransactionLogFileNameComplete, tempFileLog, true);

                _log.DebugFormat("cmdAttach: db {0} log {1}", prepareDBFileNameComplete, prepareTransactionLogFileNameComplete);

                SqlCommand cmdAttach = new SqlCommand( 
                        string.Format(@"
                        CREATE DATABASE rc_b2c_product_prepare ON
                        ( FILENAME = N'{0}' ),
                        ( FILENAME = N'{1}' )
                        FOR ATTACH", prepareDBFileNameComplete, prepareTransactionLogFileNameComplete));

                cmdAttach.Connection = prepareMasterDBConnection;
                cmdAttach.ExecuteNonQuery();

                _log.Debug("ALTER DATABASE rc_b2c_product_prepare SET MULTI_USER ");

                //set multi user 
                SqlCommand cmdOnline = new SqlCommand(@"ALTER DATABASE rc_b2c_product_prepare SET MULTI_USER WITH ROLLBACK IMMEDIATE");
                cmdOnline.Connection = prepareMasterDBConnection;
                cmdOnline.ExecuteNonQuery();

                return result;
            }
            catch (Exception e)
            {
                _log.Error(e);
                return false;
            }
            finally
            {
                if (prepareDBConnection != null)
                {
                    prepareDBConnection.Close();
                }
                if (prepareMasterDBConnection != null)
                {
                    prepareMasterDBConnection.Close();
                }
                if (readerDbFiles != null)
                {
                    readerDbFiles.Close();
                }
            }
        }

- Mathias F

Answer 1

可能是连接池出了问题，您是否尝试在执行分离/附加操作之前关闭prepareDBConnection？

其次，您是否查看过SQL管理对象（SMO）-这里有一个分离/附加的示例。

第三，当您只想制作备份副本时，无需分离数据库，可以将其设置为脱机状态。 使用SMO或使用SQL（sp_dboption doc）。

- AxelEckenberger

Answer 2

听起来可能与尝试使用不再有效的连接有关，这是由于连接池造成的。

您可以尝试关闭连接池以查看是否存在问题。要做到这一点，请在配置文件中将 "Pooling=false" 添加到 SQL 连接字符串中。

- Moe Sisko

Answer 3

分离 MSSQL 数据库

USE master;
GO
ALTER DATABASE [AdventureWorks2012] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db @dbname = N'AdventureWorks2012';
GO

附加一个分离的数据库

USE master;
GO
CREATE DATABASE MyAdventureWorks
ON (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Data.mdf'),
(FILENAME = 'C:\MySQLServer\AdventureWorks2012_Log.ldf')
FOR ATTACH;
GO

我尝试在多个数据库上运行这段代码，它工作得很好。

- Pranesh Janarthanan