At work we had a SQL Server 2008 R2 database server with about 15 or so databases on it, by default SQL had been configured to put all the *.mdf, *.ldf files in the system drive.
The goal was to move them to a secondary drive that had more than ample space. From reading the Microsoft KnowledgeBase, I realised I would have to detach the database, move the files and reattach the database from the new location. As a programmer, I’m inherently
lazy efficient, so I decided to write a batch script to do all the heavy lifting. Now, arguably the process of writing the batch took longer than it would have taken for me to just manually move the databases, but where’s the fun in that? Who knows, you may even find it useful and save some time yourself.
So here it is:
@echo off if [%3] EQU  ( echo Usage: sql_migrate ^<SQLSERVER^> ^<SOURCEFOLDER^> ^<DESTFOLDER^> GOTO:EOF ) SET SQLSERVER=%1 SET SOURCEFOLDER=%~2 SET DESTFOLDER=%~3 SET DBQRY=select DB_NAME(r.database_id), '@' + REPLACE(REPLACE(r.physical_name,'%SOURCEFOLDER%\',''),'%SOURCEFOLDER%','') AS DataFile, '@' + REPLACE(REPLACE(l.physical_name,'%SOURCEFOLDER%\',''),'%SOURCEFOLDER%','') AS LogFile from sys.master_files r inner join sys.master_files l on r.database_id = l.database_id AND l.type_desc = 'LOG' WHERE r.type_desc = 'ROWS' AND DB_NAME(r.database_id) NOT IN ('master','model','msdb','tempdb') SET TEMPFILE=%temp%\sqlmigratedb.tmp sqlcmd -S %SQLSERVER% -Q "%DBQRY%" -o "%TEMPFILE%" -W for /f "skip=2 tokens=1,2,3 delims=@" %%a in (%TEMPFILE%) do ( if NOT [%%b] EQU  ( IF EXIST "%SOURCEFOLDER%\%%b" ( echo Moving %%a sqlcmd -S %SQLSERVER% -Q "ALTER DATABASE %%a SET SINGLE_USER WITH ROLLBACK IMMEDIATE" sqlcmd -S %SQLSERVER% -Q "sp_detach_db '%%a'" copy /Y "%SOURCEFOLDER%\%%b" "%DESTFOLDER%\%%b" copy /Y "%SOURCEFOLDER%\%%c" "%DESTFOLDER%\%%c" sqlcmd -S %SQLSERVER% -Q "sp_attach_db '%%a','%DESTFOLDER%\%%b','%DESTFOLDER%\%%c'" sqlcmd -S %SQLSERVER% -Q "ALTER DATABASE %%a SET MULTI_USER " if %errorlevel% LEQ 1 ( echo Deleting Existing Files del "%SOURCEFOLDER%\%%b" del "%SOURCEFOLDER%\%%c" ) ) ) ) del "%TEMPFILE%"
It works by pulling a list of databases out of SQL, then for each database:
- Setting the database to single-user mode (Killing any existing connections)
- Detach the database
- Copy the database files to the destination folder
- Attach the database from the destination
- If all went well, delete the originals
You would then use it like:
sql_migrate.bat . "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA" d:\data Moving ONQWholesale 1 file(s) copied. 1 file(s) copied. Deleting Existing Files Moving AdventureWorks 1 file(s) copied. 1 file(s) copied. Deleting Existing Files
You can download the batch from here.