{"id":37,"date":"2013-11-01T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/detach-and-attach-user-databases"},"modified":"2026-03-18T21:50:24","modified_gmt":"2026-03-18T21:50:24","slug":"detach-and-attach-user-databases","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2013\/11\/01\/detach-and-attach-user-databases\/","title":{"rendered":"Detach and Attach User Databases"},"content":{"rendered":"<p><strong>The Need<\/strong><\/p>\n<p>I\u2019ve found myself, more times than once, needing to change disk drives that my user databases are found on. This could come up because you are moving a server and need to switch SANs, migrate from local storage to SAN storage, or need to move to a new LUN because it was over expanded and your SAN techs can\u2019t shrink it without a drop\/create.<\/p>\n<p>This exercise can be done with the GUI or with typed T-SQL but in my cases there are usually a couple of dozen databases with file in a number of different sub-directories. Today I\u2019m going to go over each method but conclude on a T-SQL script that I wrote to do almost all of the work for us.<\/p>\n<p><strong>Method #1: T-SQL<\/strong><\/p>\n<p>To detach a database and re-attach you will be using two different types of T-SQL. For the detach method you will be using the stored procedure <em>sp_detach_db\u00a0<\/em>while for the attachment you will be using a <em>CREATE DATABASE<\/em> command with the <em>FOR ATTACH<\/em> option.<\/p>\n<p>The syntax for the detachment on SQL Server 7.0 through 2012:<\/p>\n<pre><code class=\"language-\">USE master;\nGO\nEXEC sp_detach_db @dbname = N'AdventureWorks2012';\nGO<\/code><\/pre>\n<p>Syntax for the attachment on SQL Server 2005 through 2012:<\/p>\n<pre><code class=\"language-\">USE master;\nGO\nCREATE DATABASE MyAdventureWorks \n    ON (FILENAME = 'C:\\MySQLServer\\AdventureWorks2012_Data.mdf'),\n    (FILENAME = 'C:\\MySQLServer\\AdventureWorks2012_Log.ldf')\n    FOR ATTACH;\nGO<\/code><\/pre>\n<p>Syntax for the attachment on SQL Server 7.0 and 2000:<\/p>\n<pre><code class=\"language-\">use master\ngo\nsp_attach_db 'mydb','E:\\Sqldata\\mydbdata.mdf','E:\\Sqldata\\mydblog.ldf'\ngo<\/code><\/pre>\n<p><strong>Method #2: GUI via SSMS<\/strong><\/p>\n<ul>\n<li>Detach<\/li>\n<\/ul>\n<p>Right click the database that you wish to detach and select Tasks->detach.<\/p>\n<ul>\n<li><a href=\"\/wp-content\/uploads\/Detach-ssms-1.jpg\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/Detach-ssms-1.jpg\" alt=\"\" \/><\/a><\/li>\n<\/ul>\n<ul>\n<li>Check \u201cDrop Connections\u201d so that your detach won\u2019t be prone to timing out due to a persisted connection.<\/li>\n<\/ul>\n<ul>\n<li><a href=\"\/wp-content\/uploads\/Detach-ssms-2.jpg\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/Detach-ssms-2.jpg\" alt=\"\" \/><\/a><\/li>\n<\/ul>\n<ul>\n<li>Click OK.<\/li>\n<\/ul>\n<ul>\n<li>Attach<\/li>\n<\/ul>\n<p>Right click Databases and select Attach\u2026<\/p>\n<ul>\n<li><a href=\"\/wp-content\/uploads\/Attach-ssms-1.jpg\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/Attach-ssms-1.jpg\" alt=\"\" \/><\/a><\/li>\n<\/ul>\n<ul>\n<li>When the Attach Databases window opens, click Add\u2026<\/li>\n<\/ul>\n<ul>\n<li><a href=\"\/wp-content\/uploads\/Attach-ssms-2.jpg\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/Attach-ssms-2.jpg\" alt=\"\" \/><\/a><\/li>\n<\/ul>\n<ul>\n<li>Locate and select the mdf file for your database and click OK.<\/li>\n<\/ul>\n<ul>\n<li><a href=\"\/wp-content\/uploads\/Attach-ssms-3.jpg\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/Attach-ssms-3.jpg\" alt=\"\" \/><\/a><\/li>\n<\/ul>\n<ul>\n<li>When your mdf file is selected the \u201cdatabase details\u201d group box will be populated with a list of all of the files that are associated to the mdf. In my screenshot below the log file was discovered because it was in the same directory. If your ldf or ndf files are not in that same location there will be text in the \u201cMessage\u201d field that will indicate that you need to tell it where the file is. You would, in this case, click the\u00a0ellipsis\u00a0that I have highlighted and navigate to the file in question.<\/li>\n<\/ul>\n<ul>\n<li><a href=\"\/wp-content\/uploads\/Attach-ssms-4.jpg\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/Attach-ssms-4.jpg\" alt=\"\" \/><\/a><\/li>\n<\/ul>\n<ul>\n<li>Click OK and your databases will be attached.<\/li>\n<\/ul>\n<p><strong>Method #3: T-SQL Script Generation<\/strong><\/p>\n<p>Below is the script I wrote to write all of my detach and attach statements for me. It first prints out the detach statements but it queries for the database file locations and builds your attachment statements as well.<\/p>\n<p><strong>Important:<\/strong> Once you run the detach scripts you will no longer be able to query the file information so don\u2019t make the mistake of running these in the same query window as the print statements.<\/p>\n<pre><code class=\"language-\">DECLARE @name VARCHAR(50)\nDECLARE @path VARCHAR(MAX)\nDECLARE @execSQL VARCHAR(MAX)\nDECLARE @filename VARCHAR(MAX)\nDECLARE @database_ID INT\n\n<p>--Place the directory paths here\nDECLARE @newdatapath VARCHAR(MAX) = 'Q:\\InstanceName\\'\nDECLARE @newlogpath VARCHAR(MAX) = 'Z:\\InstanceName\\'<\/p>\n\n<p>--populate file locations\nSELECT name, physical_name AS CurrentLocation, database_id\nINTO #temptable\nFROM sys.master_files\nWHERE [database_id] &gt; 4<\/p>\n\n<p>--detach\nDECLARE detach_cursor CURSOR FAST_FORWARD FOR\nSELECT name, physical_name AS CurrentLocation, database_id\nFROM sys.master_files\nWHERE file_id = 1\nAND name &lt;&gt; 'master'\nAND name &lt;&gt; 'tempdev'\nAND name &lt;&gt; 'msdbdata'\nAND name &lt;&gt; 'modeldev'<\/p>\n\n<p>OPEN detach_cursor\nFETCH NEXT FROM detach_cursor INTO @name, @path, @database_ID<\/p>\n\n<p>PRINT  'USE master;' + CHAR(13) + CHAR(10)<\/p>\n\n<p>WHILE @@FETCH_STATUS = 0\nBEGIN\n\tSET @execSQL = 'EXEC sp_detach_db @dbname = N''' + DB_NAME(@database_ID) + ''';'\n\tPRINT @execSQL<\/p>\n\n<p>FETCH NEXT FROM detach_cursor INTO @name, @path, @database_ID\nEND\nCLOSE detach_cursor\nDEALLOCATE detach_cursor<\/p>\n\n<p>--attach\nPRINT CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)<\/p>\n\n<p>DECLARE attach_db_cursor CURSOR FAST_FORWARD\nFOR\n        SELECT DISTINCT [database_id]\n        FROM    #temptable<\/p>\n\n<p>OPEN attach_db_cursor\nFETCH NEXT FROM attach_db_cursor INTO @database_ID<\/p>\n\n<p>PRINT 'USE master;' + CHAR(13) + CHAR(10)<\/p>\n\n<p>WHILE ( SELECT  fetch_status\n        FROM    sys.dm_exec_cursors(@@SPID)\n        WHERE   name = 'attach_db_cursor'\n      ) = 0 \n      BEGIN<\/p>\n\n<p>SET @execSQL = 'CREATE DATABASE ' + DB_NAME(@database_ID) + ' ON' + CHAR(13) + CHAR(10)<\/p>\n\n<p>DECLARE attach_file_cursor CURSOR FAST_FORWARD\n            FOR\n                    SELECT  [name]\n                          , [CurrentLocation]\n                          , [database_id]\n                    FROM    #temptable\n                    WHERE [database_id] = @database_ID<\/p>\n\n<p>OPEN attach_file_cursor\n            FETCH NEXT FROM attach_file_cursor INTO @name, @path, @database_ID<\/p>\n\n<p>WHILE ( SELECT  fetch_status\n                    FROM    sys.dm_exec_cursors(@@SPID)\n                    WHERE   name = 'attach_file_cursor'\n                  ) = 0 \n                  BEGIN\n                        SET @filename = @path\n                        WHILE CHARINDEX('\\', @filename, 0) &lt;&gt; 0 \n                              BEGIN\n                                    SET @filename = RIGHT(@filename,LEN(@filename) - CHARINDEX('\\',@filename, 0))\n                              END<\/p>\n\n<p>IF RIGHT(@filename,3) = 'ldf'\n\t\t\t\t\t\tBEGIN\n\t\t\t\t\t\t\tSET @execSQL = @execSQL + '(FILENAME = ''' + @newlogpath + @filename + '''),' + CHAR(13) + CHAR(10)\n\t\t\t\t\t\tEND\n\t\t\t\t\t\tELSE\n\t\t\t\t\t\tBEGIN\n\t\t\t\t\t\t\tSET @execSQL = @execSQL + '(FILENAME = ''' + @newdatapath + @filename + '''),' + CHAR(13) + CHAR(10)\n\t\t\t\t\t\tEND<\/p>\n\n<p>FETCH NEXT FROM attach_file_cursor INTO @name, @path,\n                              @database_ID\n                  END<\/p>\n\n<p>SET @execSQL = LEFT(@execSQL,LEN(@execSQL)-3) + CHAR(13) + CHAR(10)\n                  SET @execSQL = @execSQL + ' FOR ATTACH;'<\/p>\n\n<p>CLOSE attach_file_cursor\n\t\t\t\t  DEALLOCATE attach_file_cursor<\/p>\n\n<p>PRINT @execSQL<\/p>\n\n<p>FETCH NEXT FROM attach_db_cursor INTO @database_ID\n      END<\/p>\n\n<p>CLOSE attach_db_cursor\nDEALLOCATE attach_db_cursor<\/p>\n\n<p>DROP TABLE #temptable<\/code><\/pre>\n<\/p>\n<p>I currently have this script setup to accept a data and log path in the variables <em>@newdatapath<\/em> and <em>@newlogpath<\/em>\u00a0at the top of the script. This is for if you know the directories that you would like to move the databases to and if they are all the same for each database. I find this situation common but I have come across situations where I wanted to move drives but not change the folder structure. In that case I would build the folder structure and then replace the below lines of the script.<\/p>\n<pre><code class=\"language-\">--Before\nIF RIGHT(@filename,3) = 'ldf'\nBEGIN\n\tSET @execSQL = @execSQL + '(FILENAME = ''' + @newlogpath + @filename + '''),' + CHAR(13) + CHAR(10)\nEND\nELSE\nBEGIN\n\tSET @execSQL = @execSQL + '(FILENAME = ''' + @newdatapath + @filename + '''),' + CHAR(13) + CHAR(10)\nEND\n\n<p>--With change\nIF RIGHT(@filename,3) = 'ldf'\nBEGIN\n\tSET @execSQL = @execSQL + '(FILENAME = ''' + REPLACE(REPLACE(@path,'E:\\','Q:\\'),'F:\\','Z:\\') + '''),' + CHAR(13) + CHAR(10)\nEND\nELSE\nBEGIN\n\tSET @execSQL = @execSQL + '(FILENAME = ''' + REPLACE(REPLACE(@path,'E:\\','Q:\\'),'F:\\','Z:\\') + '''),' + CHAR(13) + CHAR(10)\nEND<\/code><\/pre>\n<\/p>\n<p><strong>Success<\/strong><\/p>\n<p>Using method #3 I was able to relocate the database files on 2 servers with 5 instances each and 1-2 dozen databases per instance in less than 10 minutes.<\/p>\n<p>Now I just need to move the system databases.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The Need I\u2019ve found myself, more times than once, needing to change disk drives that my user databases are found on. This could come up because you are moving a server and need to switch SANs, migrate from local storage to SAN storage, or need to move to a new LUN because it was over [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":521,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-37","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/37","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=37"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/37\/revisions"}],"predecessor-version":[{"id":300,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/37\/revisions\/300"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/521"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=37"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=37"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=37"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}