What is the best way to automate a migration of multiple SQL Databases to a different hard disk? We have a SQL Server with about 50 databases. Of those databases, about 25 of them need to have their .mdf and .ldf files transferred from drive D: to drive E:. It is critical that the database names all remain the same.

What is the best, safest way to do this in an automated fashion?

My initial idea was to use a sort of pseudo ForEach Loop, supply a list of database names, and have it loop through queries to backup and restore or detach and re-attach the databases, but I have not been able to properly insert the database name variables into SQL queries within a loop.
Something as simple as the below can construct your script

SELECT
'ALTER DATABASE ' + QUOTENAME(d.name) + ' MODIFY FILE (name=N'''+[f].[name]+ ''', filename = N''' + REPLACE(f.[physical_name],'G:','E:') + ''')'

FROM sys.databases d
    INNER JOIN sys.[master_files] f ON d.[database_id] = f.[database_id]
WHERE d.[database_id] &gt; 4


You can then take the output and save it, or you could potentially wrap it further to turn it into dynamic SQL to actually run it

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'';

SELECT @SQL +=
'ALTER DATABASE ' + QUOTENAME(d.name) + ' MODIFY FILE (name=N'''+[f].[name]+ ''', filename = N''' + REPLACE(f.[physical_name],'G:','E:') + ''')'

FROM sys.databases d
    INNER JOIN sys.[master_files] f ON d.[database_id] = f.[database_id]
WHERE d.[database_id] &gt; 4

PRINT @SQL
--EXEC (@SQL)


Note the use of REPLACE to deal with the change of file location

For additional parameters and commands the above script can be edited easily to include things such as setting the database to single_user or offline.

Following this the SQL Server should be brought offline, the files moved in the file system, and the server brought back online.