Move SQL Server Database Files

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:

  1. Setting the database to single-user mode (Killing any existing connections)
  2. Detach the database
  3. Copy the database files to the destination folder
  4. Attach the database from the destination
  5. 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.

This entry was posted in SQL and tagged , , , . Bookmark the permalink.

One Response to Move SQL Server Database Files

  1. Pingback: How to move SQL Server user databases? | SQL SERVER DBA

Leave a Reply

Your email address will not be published. Required fields are marked *