SQL ServerのDBを他のDBにコピーするコマンド

SQL Serverで本番DBを開発DBにコピーするなどの用途に使えると思います。
前の担当者がSSMSのウィザードからDBコピー→リストアってしてて、そんなこと毎回絶対にしたくなかったので作りました。もし同じようなことをしている人がいたら参考にしてください。

cmdファイル中身

利用に関しては自己責任でお願いします。FROM_DBとTO_DBの順番間違えて開発環境を本番環境にコピーなどしないように・・。一連の処理に目を通して理解して使用してください。

以下のコマンドを.cmdファイルにコピーして、
SERVER_NAME、FROM_DB、TO_DB、RESTORE_BAK、LOG_PATHを変更して使ってください。管理者として実行。

@echo off
rem Change the variables according to the target.
set SERVER_NAME=YOUR_SQL_SERVER
set FROM_DB=YOUR_DB_FROM
set TO_DB=YOUR_DB_TO
set RESTORE_BAK=X:\Backups\
set LOG_PATH=C:\RefreshDB\Log\restore-%TO_DB%-%date:~4,2%%date:~7,2%%date:~10,4%.log
rem To log who ran the task.
for /f "usebackq delims=" %%A in (`whoami`) do set WHO=%%A

rem Output date and time to a log file.
echo %WHO% started refreshing the DB from %FROM_DB% to %TO_DB% at %DATE% %TIME% > %LOG_PATH%

echo. >> %LOG_PATH%
echo Backup the target file. >> %LOG_PATH%
rem FORMAT : Specifies that a new media set be created.
rem COPY_ONLY : Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups.
rem RETAINDAYS : Specifies the number of days that must elapse before this backup media set can be overwritten.
call sqlcmd -S %SERVER_NAME% -Q "BACKUP DATABASE [%FROM_DB%] TO DISK = N'%RESTORE_BAK%%FROM_DB%.bak' WITH FORMAT, COPY_ONLY, RETAINDAYS = 2, STATS = 10;" >> %LOG_PATH%

echo. >> %LOG_PATH%
echo Set offline the target database. >> %LOG_PATH%
call sqlcmd -S %SERVER_NAME% -Q "alter database [%TO_DB%] set offline with rollback immediate;" >> %LOG_PATH%

echo. >> %LOG_PATH%
echo Restore the target database. >> %LOG_PATH%
call sqlcmd -S %SERVER_NAME% -Q "RESTORE DATABASE [%TO_DB%] FROM DISK = N'%RESTORE_BAK%%FROM_DB%.bak' WITH REPLACE, STATS = 10;" >> %LOG_PATH%

echo. >> %LOG_PATH%
echo Update the logical names >> %LOG_PATH%
call sqlcmd -S %SERVER_NAME% -Q "ALTER DATABASE [%TO_DB%] MODIFY FILE (NAME = N'%FROM_DB%_Data', NEWNAME = N'%TO_DB%_Data');" >> %LOG_PATH%
call sqlcmd -S %SERVER_NAME% -Q "ALTER DATABASE [%TO_DB%] MODIFY FILE (NAME = N'%FROM_DB%_1_Data', NEWNAME = N'%TO_DB%_1_Data');" >> %LOG_PATH%
call sqlcmd -S %SERVER_NAME% -Q "ALTER DATABASE [%TO_DB%] MODIFY FILE (NAME = N'%FROM_DB%_Log', NEWNAME = N'%TO_DB%_Log');" >> %LOG_PATH%

rem Delete the .bak file.
rem start "" "%RESTORE_BAK%"
del /Q %RESTORE_BAK%%FROM_DB%.bak >> %LOG_PATH%
echo Finished refreshing the DB %TO_DB% at %DATE% %TIME% >> %LOG_PATH%

pause

参考

backup: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql
restore: https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql