Script to copy database on SQL Server
I use this script to copy the production DB to development/testing DBs on SQL Server. The previous person did this process with SSMS GUI - I didn't want to do that every time that was my motivation to create this script.
.cmd file content
Please use at your own risk. Make sure not to mix up FROM_DB and TO_DB, and accidentally copy the development environment to the production environment. Carefully review and understand the entire process before using it.
Copy the commands below to a .cmd file updating SERVER_NAME, FROM_DB, TO_DB, RESTORE_BAK, and LOG_PATH to match your environtment. Need to run it as administrator.
@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:RefreshDBLogrestore-%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
References
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
You May Also Like

Configure Microsoft Entra CBA with on-prem CA
2024年9月19日