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
- Detach
- Right click the database that you wish to detach and select Tasks->detach.
- Check “Drop Connections” so that your detach won’t be prone to timing out due to a persisted connection.
- Click OK.
- Attach
- Right click Databases and select Attach…
- When the Attach Databases window opens, click Add…
- Locate and select the mdf file for your database and click OK.
- 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.
- 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.
