Trying to detach attach database - can not acces db after

Question

I try to do the following:

• Detach the database

• Copy files to a temporary folder

• Attach the database again

This works the first time, but when I try to run this method a second time from the same process I get an error. I can always access the databse that was attached again from other clients, but not from within my application.

The error is:

"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.)", when I try to read data from sys.database_files of the newly attached db.

The error is translated from german "Fehler auf Übertragungsebene beim Senden der Anforderung an den Server. "

It happens after "cmdGetDBFileName.ExecuteReader". I can still open the connection but querying sys.database_files failes.

The source is pretty long, but I guess you can skip the part in the beginning where I get the filenames of the db to detach. Do you see my error or have any ideas what I could check?

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();
                }
            }
        }

Answer 1

To Detach a MSSQL database

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

To attach a detached database

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

I tried this code for multiple databases, it worked fine.

Answer 2

It sounds like it could be related to trying to use a connection which is no longer valid, due to connection pooling.

You could try turning off connection pooling to see if that is the problem. To do this, add "Pooling=false" to your SQL connection string in your configuration file.

Answer 3

Could be an issue with the connection pool, have you tried closing the prepareDBConnection before doing the detach / attach?

Secondly, have you looked at the SQL Management Objects (SMO) - here is an example for the detach/attach.

Thridly, you do not have to detach the database when you just want to make a backup copy, you can set it offline instead. Using SMO or using SQL (sp_dboption doc).