Query - to script the steps to move user database files around

Many times we don't pay attention while creating a user database and end up creating database files where they are not supposed to be.. in such scenarios often we find ourselves moving file across drives so as to arrange them to match our settings (or liking's ?) 

To move files of the online user database, you have to perform certain steps in the specific order and it is documented quite nicely in MSDN

This is what we do

• Take DB offline
• Move files
• Modify old file location to the new one
• bring database online

The following script generates the statements for the  steps mentioned above.. Please note that on my server I wanted to move files for the databases with a name starting from test.. you will have to change the WHERE clause so that the scripts are generated for the databases of your choice


/*
-- to move database files of the online database following steps should be followed
-- 1 : Take database offline 
-- 2 : move files (mdf,ndf,ldf) of the database to desired location
-- 3 : modify location of files so that it points to the new one
-- 4 : bring database to life

-- Following script generates the code for the above steps
-- first column has the code to take database in the offline mode
-- second column produces the code to move files.. for this make sure xp_cmdshell is enabled on your sql server.
-- third has the script to modify the filenames for the database
-- fourth column has the code to bring database online

*/

DECLARE @new_MDF_filelocation VARCHAR(1000),
@new_LDF_filelocation VARCHAR(1000)

SET @new_MDF_filelocation = 'D:\DBdataFiles\'
SET @new_LDF_filelocation = 'E:\DBLogFiles\' 

SELECT 
db_name(dbid) as databaseName
,'ALTER DATABASE ' + DB_NAME(dbid) + ' SET OFFLINE' AS [TakeDBOffline]
,'EXEC xp_cmdshell ''MOVE "' + filename + '" "' + 
CASE 
WHEN name like '%log' THEN @new_LDF_filelocation 
ELSE @new_MDF_filelocation 
END + REVERSE(SUBSTRING(reverse(filename),1,charindex('\',reverse(filename))-1)) + '"''' AS [ToMoveFiles]
,'ALTER DATABASE [' + DB_NAME(dbid) +  '] MODIFY FILE ( NAME =' + name + ', FILENAME = ''' +
CASE 
WHEN name like '%log' THEN @new_LDF_filelocation
ELSE @new_MDF_filelocation 
END + REVERSE(SUBSTRING(reverse(filename),1,charindex('\',reverse(filename))-1)) + ''')' AS [ToModifyFileLocation]
,'ALTER DATABASE ' + DB_NAME(dbid) + ' SET ONLINE' AS [BringDBOnline]
from sys.sysaltfiles
WHERE db_name(DBID) like 'test%'