Backup-Copy-Restore with one script using SQLCMD mode option in SSMS

One of the most common DBAs tasks is databases backup and restore. We do this to test the backups, to refresh test databases, to migrate the databases etc. For some of the tasks we have scripts, for others we need to create scripts on the fly.

Here I would like to share with you a simple script that you will need to run in SQL Server Management Studio’s (SSMS) SQLCMD mode. This script will allow you to create the backup, copy the backup file and restore it on the different server – all from a single SSMS window.

Start new query in SSMS and under “Query” menu select “SQLCMD Mode”:


Run this script (replace host name and SQL Server Instance name for the source and destination variables):


Note, that I commented out the “MOVE” option. Remember to add it to the script if your destination database files’ locations are different from the source.

Get the script from the SkyDrive here.