Auto-generate ALTER DATABASE statements to move SQL Server database files

This is for the DBA who needs to move a LOT of SQL Server database files, but has very little patience to sit and type each one up one by one.  Like me.  Someone I know had to move log files and adjust filegrowths for nearly 50 databases yesterday, and she tells me she began pulling her hair out about 15 minutes into it. 😣

This query is just a fast way to generate the ALTER DATABASE statements to modify file location and filegrowth settings for the given @dbName.  And, it generates the rollback as well, just in case.  Remember, always cya.  Check the variables, edit to suit your needs.

USE master;  -- run it in the master db 

SET NOCOUNT ON;

DECLARE

       @dbname SYSNAME = 'tempdb',  -- change to db you are moving

       @oldpath VARCHAR(255) =  'C:\MSSQL\2017\TempDB',  -- input existing path

       @newpath VARCHAR(255) = 'C:\MSSQL\2017\Data',  -- input new path

       @dFileGrowth CHAR(5) = '256MB',  -- change, if needed

       @lFileGrowth CHAR(5) = '128MB'  -- change, if needed

-- Generate ALTER statement to move files

SELECT + CHAR(13) + CHAR(10) +

'ALTER DATABASE ' + @dbname + '
MODIFY FILE (

       Name = ' + name + ',

       FileName = ''' + REPLACE(physical_name,@oldpath,@newpath) +''',

       FileGrowth = ' + CASE WHEN mf.type = 1 THEN @lFileGrowth ELSE @dFileGrowth END + ');'

FROM sys.master_files mf

WHERE database_id = DB_ID(@dbname);

/* Generate ROLLBACK statement for safety measures.  */

SELECT + CHAR(13) + CHAR(10) +

'ALTER DATABASE ' + @dbname + '

MODIFY FILE (

       Name = ' + name + ',

       FileName = ''' + physical_name + ''');'

FROM sys.master_files

WHERE database_id = DB_ID(@dbname);

SET NOCOUNT OFF;

Standard SSMS query to grid results will give you something like this, where you can just cut/paste into another query window to execute.

Or, if you change the SSMS query results to output to text, you can better see the structure of the ALTER statement.  Like this:

Happy SQL.  Hope to have helped!!