@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%"