Don’t Use sp_attach_db

I’ve used sp_detach_db and sp_attach_db to relocate database files for many years.  I know that sp_attach_db was deprecated in SQL 2005 but, like most DBAs, I’ve continued to use sp_attach_db mostly out of habit.  I want to share with you why I’ve decided to change my ways.

Planned File Relocation

Let’s say you want to move the log file from to a separate drive.  The following script shows how to accomplish in SQL Server 2000 using sp_attach_db.  The only sp_attach_db parameters required are the database name, primary data file path and the log file that was moved from the original location.

EXEC sp_detach_db

      @dbname = N'MyDatabase';

--move log file to E drive manually and attach from new location

EXEC sp_attach_db

      @dbname = N'MyDatabase',

      @filename1 = N'D:\DataFiles\MyDatabase_Data.mdf',

      @filename2 = N'E:\LogFiles\MyDatabase_Log.ldf';

 

The deprecated sp_attach_db procedure still works in SQL Server 2005 and SQL Server 2008 but is not recommended.  Instead, the proper method to relocate files in these later versions is with ALTER DATABASE…MODIFY FILE.  Simply execute an ALTER DATABASE…MODIFY FILE for each moved file and toggle the ONLINE/OFFLINE database state.  The script example below shows how the log file would be moved to a different drive with this method.  This method is described in detail in the Books Online.

 

ALTER DATABASE MyDatabase SET OFFLINE;

--move log file to E drive manually and attach from new location

ALTER DATABASE MyDatabase

      MODIFY FILE (

            NAME='MyDatabase_Log',

            FILENAME='E:\LogFiles\MyDatabase_Log.ldf');

ALTER DATABASE MyDatabase SET ONLINE;

 

Unfortunately, the Books Online doesn’t provide much info as to why ALTER DATABASE…MODIFY FILE and ONLINE/OFFLINE is preferred over detach/attach for planned file relocations.  One explanation is illustrated by an issue I ran into recently that motivated this post.  After using the detach/attach method, we ended up with Service Broker disabled.  This is documented behavior that we simply overlooked and didn’t catch until subsequent application problems were reported.  Since exclusive database access was needed to re-enable Service Broker, we had to close all user database connections and before altering the database ENABLE_BROKER setting and this was a real pain. 

 

This problem wouldn’t have happened had we used the recommended method and toggled the OFFLINE/ONLINE database state because the database settings would have remained unchanged.  I wouldn’t be surprised if there were other gotchas with the detach/attach method.  The bottom line is that there is no reason not to use the ALTER DATABASE…MODIFY FILE and OFFLINE/ONLINE method to move files.

Attaching a Database to Another Server or Instance

Note that the deprecated sp_attach_db stored procedure is basically just a wrapper for CREATE DATABASE…FOR ATTACH.  You can use CREATE DATABASE…FOR ATTACH much like you would sp_attach_db: specify the database name, primary file path (mdf file path) along with file paths that differ from the original locations.  For example:

EXEC sp_detach_db

      @dbname = N’MyDatabase’;

--move database files manually to new server

CREATE DATABASE MyDatabase

      ON(NAME=’MyDatabase_Data’,

            FILENAME='C:\DataFiles\MyDatabase_Data.mdf')

      LOG ON(NAME='MyDatabase_Log',

            FILENAME='C:\LogFiles\MyDatabase_Log.ldf')

      FOR ATTACH

      WITH ENABLE_BROKER;

 

The ENABLE_BROKER option is appropriate if the purpose of the attach is to completely move a SB-enabled database to another instance or in a DR scenario.  When attaching to create a database replica (e.g. copy for testing), the NEW_BROKER option is appropriate. 

Summary

I suggest ALTER DATABASE…MODIFY FILE and OFFLINE/ONLINE for planned file relocation and sp_detach_db/CREATE DATABASE…FOR ATTACH for other scenarios.  In any case, sp_attach_db should be avoided going forward.