Moving databases is a common enough task for the DBA, and there are multiple ways to move physical database files from one drive to another. Depending on your preference, you can either use the DETACH/ATTACH method:
USE AdventureWorks2012;
GO
SP_HELPFILE -- to get the logical file names and current physical file locations
USE master;
GO
SP_DETACH_DB 'AdventureWorks2012';
GO
-- Move the physical data/log files to the new location
SP_ATTACH_DB 'AdventureWorks2012', 'D:\MSSQL\DATA\AdventureWorks2012_Data.mdf','F:\MSSQL\LOG\AdventureWorks2012_log.ldf';
GO
Or you can use the ALTER DATABASE method:
ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = [AdventureWorks2012_Data], FILENAME = 'D:\MSSQL\DATA\AdventureWorks2012_Data.mdf');
ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = [AdventureWorks2012_Log], FILENAME = 'F:\MSSQL\LOG\AdventureWorks2012_log.ldf')
ALTER DATABASE [AdventureWorks2012] SET OFFLINE
-- Move the physical data/log files to the new location
ALTER DATABASE [AdventureWorks2012] SET ONLINEWhichever method you prefer, these work great when you are moving databases, especially if just a handful.
But, what if you are moving 120 databases from numerous disk drives which may or may not be well documented? This was the situation we were recently presented with at a customer site where they had acquired new SAN storage that they wanted to utilize. The 120 databases were spread across 7 different disk drives in an undocumented disarray, and a few had already been created on or moved to the new drives. So how to proceed?
First, we built a table that had all the information in it that we needed to do some analysis, and then to generate a move script. What did we need to know?
- What databases are to be moved?
- Where they are currently located?
- Where they are to be moved to?
- How much disk space is required?
So the table needed to have the following information in it for each database:
- Database id
- File id for each database file
- Filename (logical file names)
- Physical Filename
- Original Location (fully qualified physical filename)
- Original directory
- New location
- New directory
- File type (‘ROWS’ = data, ‘LOG’ = logs)
- Size in MB (for each file)
- Database status (read only, offline etc.)
In this real world scenario, the disk locations were predefined by the customer. If you are given a choice, you would like to put your data, log and tempdb files on disparate disks. Fortunately, the locations provided for this migration met that criteria.
We defined the locations where we wanted to move our database files as follows:
-- Define the drives you wish to move the data files to. Do not put '\' on the end.
DECLARE @systemDBData VARCHAR(100) = 'G:\MSSQL\SYSTEM_DATA'
,@systemDBLog VARCHAR(100) = 'F:\MSSQL\SYSTEM_LOG'
,@tempDBData VARCHAR(100) = 'E:\MSSQL\TEMPDB_DATA'
,@tempDBLog VARCHAR(100) = 'E:\MSSQL\TEMPDB_LOG'
,@userDBData VARCHAR(100) = 'G:\MSSQL\USER_DATA'
,@userDBLog VARCHAR(100) = 'F:\MSSQL\USER_LOG'We also defined all the disks that we wanted to search for existing database file by creating an array of disks as follows:
DECLARE @fromDrive TABLE(Drive CHAR(1))
--Insert the Drive(s) You want to check for database files
INSERT INTO @fromDrive (Drive) VALUES ('C'),('D'),('E'),('F'),('G'),('I'),('M')So now we were ready to gather all the data into our table. Not only does this table provide data for pre-move analysis and to create our move script, but it also provides a log of sorts.
IF OBJECT_ID( 'DBA.dbo.MOVE_Servername_Log' ) IS NOT NULL DROP TABLE DBA.dbo.MOVE_Servername_Log
SELECT
masterFiles.database_id,
masterFiles.FILE_ID,
db.name AS DatabaseName,
masterFiles.name AS FileName,
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1) AS PhysFileName,
masterFiles.physical_name AS OriginalLocation,
LEFT(masterFiles.physical_name,LEN(masterFiles.physical_name) –
CHARINDEX('\',REVERSE(masterFiles.physical_name))) AS OriginalDir,
CASE
WHEN masterFiles.type_desc = 'ROWS' AND db.name IN('master', 'model', 'msdb', 'distribution')
THEN @systemDBData + '\' +
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1)
WHEN masterFiles.type_desc = 'LOG' AND db.name IN('master', 'model', 'msdb', 'distribution')
THEN @systemDBLog + '\' +
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1)
WHEN masterFiles.type_desc = 'ROWS' AND db.name = 'tempdb'
THEN @tempDBData + '\' +
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1)
WHEN masterFiles.type_desc = 'LOG' AND db.name = 'tempdb'
THEN @tempDBLog + '\' +
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1)
WHEN masterFiles.type_desc = 'ROWS'
THEN @userDBData + '\' +
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1)
ELSE @userDBLog + '\' +
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1)
END AS NewLocation,
CASE
WHEN masterFiles.type_desc = 'ROWS' AND db.name IN('master', 'model', 'msdb', 'distribution')
THEN @systemDBData
WHEN masterFiles.type_desc = 'LOG' AND db.name IN('master', 'model', 'msdb', 'distribution')
THEN @systemDBLog
WHEN masterFiles.type_desc = 'ROWS' AND db.name = 'tempdb' THEN @tempDBData
WHEN masterFiles.type_desc = 'LOG' AND db.name = 'tempdb' THEN @tempDBLog
WHEN masterFiles.type_desc = 'ROWS' THEN @userDBData
ELSE @userDBLog
END AS NewLocationDir,
masterFiles.type_desc AS FileType,
masterFiles.size * 8 / 1024 AS Size_MB,
fdrv.Drive AS FromDrive,
' ' AS ToDrive,
CASE
WHEN DATABASEPROPERTY(db.name,'isReadOnly') = 1 THEN 'isReadOnly'
WHEN DATABASEPROPERTY(db.name,'isOffline') = 1 THEN 'isOffline'
WHEN DATABASEPROPERTY(db.name,'isSuspect') = 1 THEN 'isSuspect'
WHEN DATABASEPROPERTY(db.name,'isInRecovery') = 1 THEN 'isInRecovery'
WHEN DATABASEPROPERTY(db.name,'isInLoad') = 1 THEN 'isInLoad'
WHEN DATABASEPROPERTY(db.name,'isNotRecovered') = 1 THEN 'isNotRecovered'
ELSE 'isAvailable'
END AS DatabaseAvailable
INTO DBA.dbo.MOVE_Servername_Log
FROM sys.master_files masterFiles
JOIN sys.sysdatabases db ON masterFiles.database_id = db.dbid
JOIN @fromDrive fdrv ON LEFT(masterFiles.physical_name, 1) = fdrv.Drive
ORDER BY masterFiles.name
UPDATE DBA.dbo.MOVE_Servername_Log SET ToDrive = LEFT(NewLocation,1)
IF OBJECT_ID( 'DBA.dbo.MOVE_Servername_Log' ) IS NOT NULL DROP TABLE DBA.dbo.MOVE_Servername_Log
SELECT
masterFiles.database_id,
masterFiles.file_id,
db.name AS DatabaseName,
masterFiles.name AS FileName,
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1) AS PhysFileName,
masterFiles.physical_name AS OriginalLocation,
LEFT(masterFiles.physical_name,LEN(masterFiles.physical_name) –
CHARINDEX('\',REVERSE(masterFiles.physical_name))) AS OriginalDir,
CASE
WHEN masterFiles.type_desc = 'ROWS' AND db.name IN ('master', 'model', 'msdb', 'distribution')
THEN @systemDBData + '\' +
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1)
WHEN masterFiles.type_desc = 'LOG' AND db.name IN ('master', 'model', 'msdb', 'distribution')
THEN @systemDBLog + '\' +
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1)
WHEN masterFiles.type_desc = 'ROWS' AND db.name = 'tempdb'
THEN @tempDBData + '\' +
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1)
WHEN masterFiles.type_desc = 'LOG' AND db.name = 'tempdb'
THEN @tempDBLog + '\' +
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1)
WHEN masterFiles.type_desc = 'ROWS'
THEN @userDBData + '\' +
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1)
ELSE @userDBLog + '\' +
RIGHT(masterFiles.physical_name,CHARINDEX('\',REVERSE(masterFiles.physical_name))-1)
END AS NewLocation,
CASE
WHEN masterFiles.type_desc = 'ROWS' AND db.name IN ('master', 'model', 'msdb', 'distribution')
THEN @systemDBData
WHEN masterFiles.type_desc = 'LOG' AND db.name IN ('master', 'model', 'msdb', 'distribution')
THEN @systemDBLog
WHEN masterFiles.type_desc = 'ROWS' AND db.name = 'tempdb' THEN @tempDBData
WHEN masterFiles.type_desc = 'LOG' AND db.name = 'tempdb' THEN @tempDBLog
WHEN masterFiles.type_desc = 'ROWS' THEN @userDBData
ELSE @userDBLog
END AS NewLocationDir,
masterFiles.type_desc AS FileType,
masterFiles.size * 8 / 1024 AS Size_MB,
fdrv.Drive AS FromDrive,
' ' AS ToDrive,
CASE
WHEN DATABASEPROPERTY(db.name,'isReadOnly') = 1 THEN 'isReadOnly'
WHEN DATABASEPROPERTY(db.name,'isOffline') = 1 THEN 'isOffline'
WHEN DATABASEPROPERTY(db.name,'isSuspect') = 1 THEN 'isSuspect'
WHEN DATABASEPROPERTY(db.name,'isInRecovery') = 1 THEN 'isInRecovery'
WHEN DATABASEPROPERTY(db.name,'isInLoad') = 1 THEN 'isInLoad'
WHEN DATABASEPROPERTY(db.name,'isNotRecovered') = 1 THEN 'isNotRecovered'
ELSE 'isAvailable'
END AS DatabaseAvailable
INTO DBA.dbo.MOVE_Servername_Log
FROM sys.master_files masterFiles
JOIN sys.sysdatabases db ON masterFiles.database_id = db.dbid
JOIN @fromDrive fdrv ON LEFT(masterFiles.physical_name, 1) = fdrv.Drive
ORDER BY masterFiles.name
UPDATE DBA.dbo.MOVE_Servername_Log SET ToDrive = LEFT(NewLocation,1)Once the table was created we could do some analysis on how much disk space was being moved to each drive:
-- Get disk space totals ----------------------------------------------------------
SELECT ToDrive, CASE FileType WHEN 'ROWS' THEN 'DATA' ELSE FileType END AS FileType, SUM(size_MB) AS MBPerDrive
FROM DBA.dbo.MOVE_Servername_Log
WHERE FromDrive != ToDrive
GROUP BY ToDrive, FileType
UNION
SELECT ToDrive, 'All' AS FileType, SUM(size_MB) AS MBPerDrive
FROM DBA.dbo.MOVE_Servername_Log
WHERE FromDrive != ToDrive
GROUP BY ToDrive
UNION
SELECT '---- Total' AS ToDrive, 'All' AS FileType, SUM(size_MB) AS MBPerDrive
FROM DBA.dbo.MOVE_Servername_Log
WHERE FromDrive != ToDrive
ORDER BY ToDrive DESC,FileType DESCOnce the analysis was done, we were ready create our move script. Since we were moving both system and user databases, we wanted to script and move them separately. We decided to use the ALTER DATABASE method rather than the DETACH/ATTACH method described above. So, for the system database files, the commands would be as follows:
ALTER DATABASE [master] MODIFY FILE (NAME = master,
FILENAME = 'G:\SQL_MDF_SYSTEM_DATA\master.mdf' )
ALTER DATABASE [master] MODIFY FILE (NAME = mastlog,
FILENAME = 'F:\SQL_LDF_SYSTEM_DATA\mastlog.ldf' )Then shutdown SQL Server, copy the physical files to the new locations, and restart SQL Server.
IMPORTANT: Before moving any database files, make sure that the SQL Server Services login has permissions in each of the directories.
The user databases will be similar but rather than taking SQL Server down, you take each of the databases offline, move the physical files, then bring the databases back online. You may have to create or download a script for killing connections, but that is not provided here.
ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = [AdventureWorks2012_Data], FILENAME = 'G:\SQL_MDF_DATA\AdventureWorks2012_Data.mdf' )
ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = [AdventureWorks2012_Log], FILENAME = 'F:\SQL_LDF_DATA\AdventureWorks2012_log.ldf' )
ALTER DATABASE [AdventureWorks2012] SET OFFLINEFor the script creation, we created a temporary table with database name, sort order (so commands display in correct order), and the actual script command. Also, we wanted to script out the robocopy commands for the physical moves, so we defined a log file:
-- Define the location and name of the robocopy log
DECLARE @robocopyLog VARCHAR(100) = 'G:\MSSQL\robocopy.log'The script was built as follows:
-- Build System DB move script ------------------------------------------------------------------------
INSERT INTO ##Script(Sort,Script) VALUES (0,'--------- Begin System Database Move Script ---------------')
INSERT INTO ##Script(DatabaseName, Sort,Script)
SELECT DISTINCT DatabaseName, 1,' ALTER DATABASE ' + QUOTENAME(DatabaseName) + ' MODIFY FILE (NAME = ' + FileName + ', FILENAME = ''' + NewLocation + ''' )'
FROM DBA.dbo.MOVE_Servername_Log
WHERE OriginalLocation <> NewLocation
AND DatabaseAvailable = 'isAvailable'
AND database_id <= 4
INSERT INTO ##Script(Sort,Script) VALUES (2,'-----------------------------------------------------------')
INSERT INTO ##Script(Sort,Script) VALUES (3,'-- COPY Physical files. Run the following commands in a CMD window. Run as Administrator!')
INSERT INTO ##Script(Sort,Script) VALUES (4,'-----------------------------------------------------------')
INSERT INTO ##Script(DatabaseName, Sort, Script)
SELECT DISTINCT DatabaseName, 5,' robocopy "'
+ OriginalDir + '" "'
+ NewLocationDir + '" "'
+ PhysFileName + '" /COPYALL /NJS /LOG+:"'
+ @robocopyLog + '"'
FROM DBA.dbo.MOVE_Servername_Log
WHERE OriginalLocation <> NewLocation
AND DatabaseAvailable = 'isAvailable'
AND database_id <= 4
INSERT INTO ##Script(Sort,Script) VALUES (6,'------------------- End System Script ---------------------')
-- END Build User DB move script -----------------------------------------------------------------------
-- Build User DB move script ---------------------------------------------------------------------------
INSERT INTO ##Script(Sort,Script) VALUES (7,'------------ Begin User Database Move Script --------------')
INSERT INTO ##Script(DatabaseName, Sort,Script)
SELECT DISTINCT DatabaseName, 8,' ALTER DATABASE ' + QUOTENAME(DatabaseName) + ' MODIFY FILE (NAME = [' + FileName + '], FILENAME = ''' + NewLocation + ''' )'
FROM DBA.dbo.MOVE_Servername_Log
WHERE OriginalLocation <> NewLocation
AND DatabaseAvailable = 'isAvailable'
AND database_id > 4
INSERT INTO ##Script(DatabaseName, Sort,Script)
SELECT DISTINCT DatabaseName, 8,' ALTER DATABASE ' + QUOTENAME(DatabaseName) + ' SET OFFLINE'
FROM DBA.dbo.MOVE_Servername_Log
WHERE OriginalLocation <> NewLocation
AND DatabaseAvailable = 'isAvailable'
AND database_id > 4
INSERT INTO ##Script(Sort,Script) VALUES (9,'-----------------------------------------------------------')
INSERT INTO ##Script(Sort,Script) VALUES (10,'-- COPY Physical files. Run the following commands in a CMD window. Run as Administrator!')
INSERT INTO ##Script(Sort,Script) VALUES (11,'----------------------------------------------------------')
INSERT INTO ##Script(DatabaseName, Sort, Script)
SELECT DISTINCT DatabaseName, 12,' robocopy "'
+ OriginalDir + '" "'
+ NewLocationDir + '" "'
+ PhysFileName + '" /COPYALL /NJS /LOG+:"'
+ @robocopyLog + '"'
FROM DBA.dbo.MOVE_Servername_Log
WHERE OriginalLocation <> NewLocation
AND DatabaseAvailable = 'isAvailable'
AND database_id > 4
INSERT INTO ##Script(Sort,Script) VALUES (13,'----------------------------------------------------------')
INSERT INTO ##Script(Sort,Script) VALUES (14,'-- Bring the databases back online.')
INSERT INTO ##Script(Sort,Script) VALUES (15,'----------------------------------------------------------')
INSERT INTO ##Script(DatabaseName, Sort, Script)
SELECT DISTINCT DatabaseName, 16,' ALTER DATABASE ' + QUOTENAME(DatabaseName) + ' SET ONLINE'
FROM DBA.dbo.MOVE_Servername_Log
WHERE OriginalLocation <> NewLocation
AND DatabaseAvailable = 'isAvailable'
AND database_id > 4
INSERT INTO ##Script(Sort,Script) VALUES (17,'--------------------- End User Script --------------------')
-- END Build User DB move script -----------------------------------------------------------------------Now that the script has been created in the ##Script table, you can extract it by running the following SELECT statement to a text output in SSMS:
SELECT Script
FROM ##Script
ORDER BY Sort, DatabaseName, ScriptSo in conclusion, moving multiple databases is not difficult if the task is approached with caution and plenty of forethought. The scripts and details defined herein provide one methodology for completing the task, but ultimately it is up to you to choose how you want to make this move. As always, proceed with caution!
