A Script for Migrating SQL Data (and Log) Files

Suppose that you’ve been asked to migrate 166 databases’ .mdf files from one drive to another.  How would you do that?

I’d generate a list based on the .mdf files (the databases are all named the same thing as the .mdf.  If this wasn’t the case, we’d have to query SQL for it–“select name from sys.databases”–and get the file name out of the database’s sys.database_files table) and do this:

@ECHO OFF

set controlfile=test.txt

SETLOCAL DISABLEDELAYEDEXPANSION
FOR /F %%L IN (%controlfile%%) DO (
SET “line=%%L”
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO !line:.mdf=!
sqlcmd -E -S.\prod1 -Q”EXEC master.dbo.sp_detach_db @dbname = N’!line:.mdf=!'”
copy “C:\SQL Server Data\Prod1\Data\!line!” “D:\SQL Server Data\Prod1\Data”
sqlcmd -E -S.\prod1 -Q”CREATE DATABASE [!line:.mdf=!] ON ( FILENAME = N’D:\SQL Server Data\Prod1\Data\!line!’ ),( FILENAME = N’C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf’ ) FOR ATTACH”
move “C:\SQL Server Data\Prod1\Data\!line!” “C:\SQL Server Data\Prod1\Data\moved\”
ENDLOCAL
)
ENDLOCAL

(Download.)

In this example, the logs were then moved, too.

@ECHO OFF

set controlfile=test.txt

SETLOCAL DISABLEDELAYEDEXPANSION
FOR /F %%L IN (%controlfile%%) DO (
SET “line=%%L”
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO !line:.mdf=!
sqlcmd -E -S.\prod1 -Q”EXEC master.dbo.sp_detach_db @dbname = N’!line:.mdf=!'”
copy “C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf” “F:\SQL Server Data\Prod1\Log\”
sqlcmd -E -S.\prod1 -Q”CREATE DATABASE [!line:.mdf=!] ON ( FILENAME = N’D:\SQL Server Data\Prod1\Data\!line!’ ),( FILENAME = N’F:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf’ ) FOR ATTACH”
move “C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf” “C:\SQL Server Data\Prod1\Log\moved\”
ENDLOCAL
)
ENDLOCAL

(Download.)

You could always combine both actions in the same script, too.  There was a reason to move the files separately, but we could move them together and add a backup to the script:

@ECHO OFF

set controlfile=test.txt

SETLOCAL DISABLEDELAYEDEXPANSION
FOR /F %%L IN (%controlfile%%) DO (
SET “line=%%L”
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO !line:.mdf=!
sqlcmd -E -S.\prod1 -Q”BACKUP DATABASE [!line:.mdf=!] TO DISK = N”F:\SQL backups\!line:.mdf=!.bak” WITH COPY_ONLY, WITH FORMAT, INIT, NAME = N”!line:.mdf=!-Full Database Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10″
sqlcmd -E -S.\prod1 -Q”EXEC master.dbo.sp_detach_db @dbname = N’!line:.mdf=!'”
copy “C:\SQL Server Data\Prod1\Data\!line!” “D:\SQL Server Data\Prod1\Data”
copy “C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf” “F:\SQL Server Data\Prod1\Log\”
sqlcmd -E -S.\prod1 -Q”CREATE DATABASE [!line:.mdf=!] ON ( FILENAME = N’D:\SQL Server Data\Prod1\Data\!line!’ ),( FILENAME = N’F:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf’ ) FOR ATTACH”
move “C:\SQL Server Data\Prod1\Data\!line!” “C:\SQL Server Data\Prod1\Data\moved\”
move “C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf” “C:\SQL Server Data\Prod1\Log\moved\”
ENDLOCAL
)
ENDLOCAL

(Download.)

How would you do it?