Detach and Attach User Databases

The Need

I’ve 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’t shrink it without a drop/create.

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’m 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.

Method #1: T-SQL

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 sp_detach_db while for the attachment you will be using a CREATE DATABASE command with the FOR ATTACH option.

The syntax for the detachment on SQL Server 7.0 through 2012:

USE master;
GO
EXEC sp_detach_db @dbname = N'AdventureWorks2012';
GO

Syntax for the attachment on SQL Server 2005 through 2012:
USE master;
GO
CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Data.mdf'),
    (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Log.ldf')
    FOR ATTACH;
GO

Syntax for the attachment on SQL Server 7.0 and 2000:
use master
go
sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
go

Method #2: GUI via SSMS

  1. Detach
    1. Right click the database that you wish to detach and select Tasks->detach.
    2. Check “Drop Connections” so that your detach won’t be prone to timing out due to a persisted connection.
    3. Click OK.
  2. Attach
    1. Right click Databases and select Attach…
    2. When the Attach Databases window opens, click Add…
    3. Locate and select the mdf file for your database and click OK.
    4. When your mdf file is selected the “database details” 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 “Message” field that will indicate that you need to tell it where the file is. You would, in this case, click the ellipsis that I have highlighted and navigate to the file in question.
    5. Click OK and your databases will be attached.

Method #3: T-SQL Script Generation

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.

Important: Once you run the detach scripts you will no longer be able to query the file information so don’t make the mistake of running these in the same query window as the print statements.

DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(MAX)
DECLARE @execSQL VARCHAR(MAX)
DECLARE @filename VARCHAR(MAX)
DECLARE @database_ID INT

--Place the directory paths here
DECLARE @newdatapath VARCHAR(MAX) = 'Q:\InstanceName\'
DECLARE @newlogpath VARCHAR(MAX) = 'Z:\InstanceName\'

--populate file locations
SELECT name, physical_name AS CurrentLocation, database_id
INTO #temptable
FROM sys.master_files
WHERE [database_id] > 4

--detach
DECLARE detach_cursor CURSOR FAST_FORWARD FOR
SELECT name, physical_name AS CurrentLocation, database_id
FROM sys.master_files
WHERE file_id = 1
AND name <> 'master'
AND name <> 'tempdev'
AND name <> 'msdbdata'
AND name <> 'modeldev'

OPEN detach_cursor
FETCH NEXT FROM detach_cursor INTO @name, @path, @database_ID

PRINT  'USE master;' + CHAR(13) + CHAR(10)

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @execSQL = 'EXEC sp_detach_db @dbname = N''' + DB_NAME(@database_ID) + ''';'
	PRINT @execSQL

	FETCH NEXT FROM detach_cursor INTO @name, @path, @database_ID
END
CLOSE detach_cursor
DEALLOCATE detach_cursor

--attach
PRINT CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

DECLARE attach_db_cursor CURSOR FAST_FORWARD
FOR
        SELECT DISTINCT [database_id]
        FROM    #temptable

OPEN attach_db_cursor
FETCH NEXT FROM attach_db_cursor INTO @database_ID

PRINT 'USE master;' + CHAR(13) + CHAR(10)

WHILE ( SELECT  fetch_status
        FROM    sys.dm_exec_cursors(@@SPID)
        WHERE   name = 'attach_db_cursor'
      ) = 0 
      BEGIN

			SET @execSQL = 'CREATE DATABASE ' + DB_NAME(@database_ID) + ' ON' + CHAR(13) + CHAR(10)

            DECLARE attach_file_cursor CURSOR FAST_FORWARD
            FOR
                    SELECT  [name]
                          , [CurrentLocation]
                          , [database_id]
                    FROM    #temptable
                    WHERE [database_id] = @database_ID

            OPEN attach_file_cursor
            FETCH NEXT FROM attach_file_cursor INTO @name, @path, @database_ID

            WHILE ( SELECT  fetch_status
                    FROM    sys.dm_exec_cursors(@@SPID)
                    WHERE   name = 'attach_file_cursor'
                  ) = 0 
                  BEGIN
                        SET @filename = @path
                        WHILE CHARINDEX('\', @filename, 0) <> 0 
                              BEGIN
                                    SET @filename = RIGHT(@filename,LEN(@filename) - CHARINDEX('\',@filename, 0))
                              END

						IF RIGHT(@filename,3) = 'ldf'
						BEGIN
							SET @execSQL = @execSQL + '(FILENAME = ''' + @newlogpath + @filename + '''),' + CHAR(13) + CHAR(10)
						END
						ELSE
						BEGIN
							SET @execSQL = @execSQL + '(FILENAME = ''' + @newdatapath + @filename + '''),' + CHAR(13) + CHAR(10)
						END

                        FETCH NEXT FROM attach_file_cursor INTO @name, @path,
                              @database_ID
                  END

                  SET @execSQL = LEFT(@execSQL,LEN(@execSQL)-3) + CHAR(13) + CHAR(10)
                  SET @execSQL = @execSQL + ' FOR ATTACH;'

                  CLOSE attach_file_cursor
				  DEALLOCATE attach_file_cursor

				  PRINT @execSQL

             FETCH NEXT FROM attach_db_cursor INTO @database_ID
      END

CLOSE attach_db_cursor
DEALLOCATE attach_db_cursor

DROP TABLE #temptable

I currently have this script setup to accept a data and log path in the variables @newdatapath and @newlogpath at 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.
--Before
IF RIGHT(@filename,3) = 'ldf'
BEGIN
	SET @execSQL = @execSQL + '(FILENAME = ''' + @newlogpath + @filename + '''),' + CHAR(13) + CHAR(10)
END
ELSE
BEGIN
	SET @execSQL = @execSQL + '(FILENAME = ''' + @newdatapath + @filename + '''),' + CHAR(13) + CHAR(10)
END

--With change
IF RIGHT(@filename,3) = 'ldf'
BEGIN
	SET @execSQL = @execSQL + '(FILENAME = ''' + REPLACE(REPLACE(@path,'E:\','Q:\'),'F:\','Z:\') + '''),' + CHAR(13) + CHAR(10)
END
ELSE
BEGIN
	SET @execSQL = @execSQL + '(FILENAME = ''' + REPLACE(REPLACE(@path,'E:\','Q:\'),'F:\','Z:\') + '''),' + CHAR(13) + CHAR(10)
END

Success

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.

Now I just need to move the system databases.

Leave a Reply

%d bloggers like this: