Copy and restore database with bat-file

In our development environment, we have a couple of test-servers and a development server. Each server runs an instance of our application and each has their own database.

It is nice to test new features on live data or data from the test-server. It requires grabbing a copy of a relevant database backup, copying it and restoring it to the database used with local development. That can be a cumbersome procedure.

This script uses Robocopy  and sqlcmd to get the source database and restoring it to the target database. It locates the newest file in the source directory.

It accepts three parameters:

  • Folder of source database backup file.
  • Name of source database.
  • Name of target database.

You need to setup a couple of variables to match your environment:

  • DatabaseBackupPath. Path of the source database backup file, e.g. \\myserver\h$\Backup\SQL\
  • LocalBakPath. Path to the folder where the copied backup of the source database should be placed, e.g. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\BAKsFromNetwork
  • LocalDbPath. Path to the folder where SQL Server stores database data files, e.g. c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
@ECHO OFF
:Variables
rem Name of folder in which source database backup file is stored.
SET Source_DbFolderName=%1

rem Name of source database in database backup file.
SET Source_DbName=%2

rem Name of target database to restore source database to.
SET Target_DbName=%3
 
IF [%1] == [] GOTO :LblInput
IF [%2] == [] GOTO :LblInput
IF [%3] == [] GOTO :LblInput
 
SET DatabaseBackupPath=[network path]%Source_DbFolderName%
SET LocalBakPath=[local path where Robocopy should save the copied bak-file]
SET LocalDbPath=[local path where SQL Server searches for bak-files]
  
rem Create target database; fail silently if it already exist
sqlcmd -E -S . -Q "CREATE DATABASE %Target_DbName%"
 
echo.
echo Copy Database
FOR /F "delims=|" %%I IN ('DIR %DatabaseBackupPath%\*.bak /OD /B') DO SET NewestFile=%%I
 
IF [%NewestFile%] == [] GOTO :LblNoFile
 
robocopy "%DatabaseBackupPath%" "%LocalBakPath%" %NewestFile%
 
echo.
echo Restore Database
sqlcmd -E -d master -Q "ALTER DATABASE [%Target_DbName%] SET SINGLE_USER WITH ROLLBACK IMMEDIATE" 
sqlcmd -E -d master -Q "RESTORE DATABASE [%Target_DbName%] FROM  DISK = N'%LocalBakPath%\%NewestFile%' WITH  FILE = 1, MOVE N'%Source_DbName%' TO N'%LocalDbPath%\%Target_DbName%.mdf', MOVE N'%Source_DbName%_log' TO N'%LocalDbPath%\%Target_DbName%_log.ldf', NOUNLOAD, REPLACE,  STATS = 10"
sqlcmd -E -d master -Q "ALTER DATABASE [%Target_DbName%] SET MULTI_USER" 
 
ECHO.
ECHO DONE.
PAUSE
GOTO :lblEnd
 
:LblInput
ECHO SPECIFY PARAMETERS! First parameter is Source_DbFolderName, second parameter is Source_DbName, third parameter is Target_DbName
PAUSE
GOTO :lblEnd
 
:LblNoFile
ECHO No Source File Found In '%DatabaseBackupPath%'
PAUSE
 
:lblEnd

Skip to toolbar