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.
Legacy Comments
|
Soma
2009-01-29 |
re: Don’t Use sp_attach_db Thanks for the good tip. |
|
rina
2009-04-10 |
re: Don’t Use sp_attach_db i had prob. with pendrive database which some one deleted and i recover it by usbreader but now it waas not attach to sql 7.0 shows error for invalid header file |
|
Dan d
2009-04-27 |
re: Don’t Use sp_attach_db I might have by accident found why sp_detach_db is deprecated. I started down the path of the dark side, and used sp_detatch_db at first, and was surprised to find the mdf file automagically was deleted, huh? I was surprised to see the datafile disapear. Not sure if anyone eles has run into this problem as well, it seems the Alter/Toggon [on|off] makes more sense if for the server they are boolean flag if the database is online or not... |
|
Dan Guzman
2009-04-28 |
re: Don’t Use sp_attach_db Hi, Dan. I didn't mention anything about sp_detach_db in this post but you are correct that it is deprecated in SQL Server 2008. One can use ALTER DATABASE...SET OFFLINE followed by DROP DATABASE to accomplish the same result. Even though sp_detach_db is deprecated, it should still work as expected; the database files should remain on the file system. I have not heard of a data file disappearing with sp_detach_db as you experienced. |
|
Dan d
2009-05-11 |
re: Don’t Use sp_attach_db I found out what my problem was, the code I contained a bug when it used Path.Combine(), as a result the path for the mdf file to delete was wrong and it was deleting the wrong mdf file on me. I decided to go with the "Alter Database set offline" method and only ran into two other issue. Since the database being detached was in a multi-use environment I needed to use "SET SINGLE_USER WITH ROLLBACK IMMEDIATE " to force taking the DB, offline. After this when reattaching I needed to use "SET MULTI_USER WITH NO_WAIT" and all was good. I'm not sure if the "ENABLE_BROKER" has the same effect or is someway an alias for "SET MULTI_USER WITH NO_WAIT" which I ended up using. I did see comments about people having issues with "ENABLE_BROKER", so I feel the "SET MUILTI..." is better/safer. |
|
Pinal Dave
2009-05-25 |
re: Don’t Use sp_attach_db Very nice. I really enjoying reading this article. Kind Regards, Pinal |
|
deep
2010-03-29 |
re: Don’t Use sp_attach_db hi. i want to attach a database to a pendrive which can be used from different computers.. the database should not be present on any of computers hard drives. Plz help asap... Deep |
|
Dan Guzman
2010-03-29 |
re: Don’t Use sp_attach_db Yes, you can attach a database from a removable device, such as a pendrive. Just run the CREATE DATABASE...FOR ATTACH command after inserting the media. However, it is recommended that you use local disk, iSCSI or SAN storage for performance and reliability, especially if the database is not read-only. |
|
webwilliam
2010-11-18 |
re: Don’t Use sp_attach_db I just made the mistake of using the detach/attach method. Besides disabling the service broker, it also automatically changed the database TRUSTWORTHY to OFF and messed up the related CLR Assemblies. I had to kick everyone out to reset the Broker and I had to reinstall the assemblies and update the permission_set for them. |
|
Ravi
2011-02-20 |
re: Don’t Use sp_attach_db Hi Dan Very good article! It is very helpful. I just want to add couple of points- SQL Server forgets about the database when using sp_detachdb to detach a database. So it will let you create a new database with same name ( have to use different physical ldf and mdf file names, since the detached physical files are still lying in the data and log path unless they are removed.) Where you use alter database to set offline, SQL Server does not forget about the database. System catalogs have the database information and mealy marks it is offline. To summarize- Equivalent of sp_detachdb = ( alter database set offline + drop database) Equivalent of sp_attachdb = ( create database with for attach option) |
|
Indareddy
2011-04-29 |
re: Don’t Use sp_attach_db Thank u very Much..this article is very helpful for me...thanks alot |