Moving user databases the TSQL way

Here’s a few queries I built to construct the commands needed to move user database files in SQL Server 2208 R2. The queries are based on the procedure outlined here. As with all scripts on the Internet take care with this. It worked fine for my circumstances but may not in yours. Be careful and take backups!

Select the appropriate user databases by database_id

SELECT *
FROM sys.databases;

Take databases offline.

-- set specific user databases to move offline. This will rollback any active transactions
-- to put it into an offline status quicker.
SELECT 'ALTER DATABASE [' + [name] + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;'
FROM sys.databases
WHERE database_id IN (5,6,7,8,9,10,11,12,13,14,15);

Generate file move commands.

-- Generate move commands for dos. Different locations for data and log files
-- Ensure these are appropriate for your system
SELECT 'move "' + mf.physical_name + '" ' +
		CASE
			WHEN mf.type_desc = 'ROWS' THEN '"G:\SQLData\'
			WHEN mf.type_desc = 'LOG' THEN '"H:\SQLLogs\'
		END + RIGHT(mf.physical_name, CHARINDEX('\', REVERSE(mf.physical_name))-1)  + '"'
FROM sys.master_files mf
INNER JOIN sys.databases d
	ON d.database_id = mf.database_id
WHERE d.database_id IN (5,6,7,8,9,10,11,12,13,14,15);

Now move commands for MSSQL.

-- Move command for mssql
SELECT 'ALTER DATABASE [' + d.[name] + '] MODIFY FILE ( NAME = ' + mf.[name] + ', FILENAME = ''' + 
		CASE
			WHEN mf.type_desc = 'ROWS' THEN 'G:\SQLData\'
			WHEN mf.type_desc = 'LOG' THEN 'H:\SQLLogs\'
		END + RIGHT(mf.physical_name, CHARINDEX('\', REVERSE(mf.physical_name))-1)  + ''');'
FROM sys.master_files mf
INNER JOIN sys.databases d
	ON d.database_id = mf.database_id
WHERE d.database_id IN (5,6,7,8,9,10,11,12,13,14,15);

Now bbring your user databases back online.

-- Return user databases to online status
SELECT 'ALTER DATABASE [' + [name] + '] SET ONLINE;'
FROM sys.databases
WHERE database_id IN (5,6,7,8,9,10,11,12,13,14,15);

UPDATE: My syntax highlighter has got confused over this TSQL. I’ve confirmed they copy’n’paste into SSMS ok.


Leave a Reply