Prashanth Jayaram

Discussing Backup and Restore Automation using SQLCMD and SQL Server agent

May 3, 2018 by

Database administrators are often requested to refresh a database, mostly to create a live copy of the production database to the test or development environment. This is done so that the development or the test environment closely resembles the production environment; this way, we prevent many undesirable issues when running the application in the production environment.

Many developers, at times, are required to write and debug code on the production copy of the database, so it makes more sense to refresh the database on regular basis.

Let’s build the process to automate the database refresh activity using a simple backup-and-restore method and scheduling it using SQL Server Agent.

  1. Restore the database to the same server with different name

  2. Restore the database to different server using sqlcmd and Robocopy utility

  3. Schedule the job

Let’s take a closer look at the process to see how this works. 

First, create a new database, ProdSQLShackDemo, and a table, SQLShackAuthor. Let’s go ahead and populate the SQLShackauthor table with some records. Now, back this up to a desired location; in this case it’s F:\PowerSQL\. Let’s name this full backup as ProdSQLShackDemo.BAK.

The backup database command is used with the FORMAT clause. The format option overwrites any existing backups and creates a new backup set.

The RESTORE FILELISTONLY SQL requires a path to the backup file. This will return a table with corresponding logical and physical records of each file inside of the backup.

In this section, we will see how to restore the database with a different name: TestSQLShackDemo.

  1. Use the F:\PowerSQL\ProdSQLShackDemo.bak as a reference backup file.
  2. The MOVE clause is used to move the files to a different file location.
  3. The keyword RECOVERY is used, since we don’t have any further backups to restore.

We can see that the new TestSQLShackDemo has been created.

To verify the restoration process, select everything out of these two tables. 

Restore the database to a different server. This is to simulate the real time scenario: we back up the production database and restore it to the test environment.

We use SQLCMD for the proof of concept. Let’s now go ahead and automate this process.

  1. Declare the variable
    1. Source production database: DB
    2. Source server: SRC
    3. Target server: TGT
    4. Source database backup path: BACKUP_PATH
    5. Target database restore path: RESTORE_PATH
    6. Source database data file name: DATAFILENAME
    7. Source database log filename: LOGFILENAME
    8. Target server data file location: RESTORE_DATA_PATH
    9. Target Server log file location: RESTORE_LOG_PATH
  2. Connect to source and target to check the existence of the database
  3. Backup the database
  4. Use the robocopy utility for the copy operation. This way we quickly transfer data across the network.

  5. Before restoring, check the existence of the target database, and then use the alter database statement to set the target database to single user mode; issue a drop database command to drop the target database.
  6. Restore the database
  7. Validate the output

In the first run, the target database was created for the first time. You can check the create_date column to confirm. In the first result set we can see the absence of the target database. The result is empty.

In the second result, the database was created.

In the second run, though, the database exists at the target SQL instance (because of the first run). Therefore, the target database is dropped and recreated during this run. Notice the create_date column; the first highlight is after the first run. In the second highlight, we see that ProdSQLShackDemo’s create_date is different, which shows that the database was dropped and created again.

Automate the backup and restore process using a SQL Server agent job

To automate this process follow the below steps

  1. Save the sqlcmd script to a file BackupandRestoreAutomation.sql
  2. To test that sqlcmd is working, execute the SQL Script file from the command prompt.

After successful execution, proceed with the following steps

  1. Create a new job on the SQL server agent.
    1. Browse object explorer, expand the SQL Server agent folder.
    2. Select the jobs folder and right click and create a New Job…

  2. In the general properties, enter the name of the job. Let’s call it BackupandRestoreAutomation.

  3. Switch the selection on the left from general to steps 
    1. In the general tab of the Job Steps, enter the step name; in this case, the step name is “Execute the sqlcmd script”
    2. Choose the type as Operating system (CmdExec)
    3. In the command option, type the command that we want to execute; in this case it’s the sqlcmd command that invokes the script file using the –i option.

  4. Click on the OK button. 
  5. Next, switch the selection on the left from steps to schedules
  6. To add a schedule, click the New button
  7. The name of the schedule is BackupAndRestoreSchedule. Choose the frequency and best time to run the job as per your requirements.
  8. Click OK to save the schedule

  9. The job is now created.

Instead of waiting, let’s manually activate these jobs. Right-click and choose the start job option:

This takes care of the automated backup and restore of the databases.

Wrapping up

In this article, we carried out a backup-and-restore of a database in two ways:

  1. Using SQLCMD with the source and target being on the same instance on the same server.
  2. Using SQLCMD and SQL Server Agent, wherein the source and the target were on different machines. We used the ROBOCOPY utility to perform the database backup file transfer.

That’s all for now. Stay tuned for more on SQL Server backups!

Table of contents

Database Backup and Restore process in SQL Server – series intro
An overview of the process of SQL Server backup-and-restore
Understanding the SQL Server Data Management Life Cycle
Understanding SQL Server database recovery models
Understanding SQL Server Backup Types
Backup and Restore (or Recovery) strategies for SQL Server database
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
Understanding Database snapshots vs Database backups in SQL Server
SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
Smart database backup in SQL Server 2017
How to perform a Page Level Restore in SQL Server
Backup Linux SQL databases Using PowerShell and Windows Task Scheduler
SQL Server database backup and restore operations using the Cloud
Tail-Log Backup and Restore in SQL Server
SQL Server Database Backup and Restore reports
Database Filegroup(s) and Piecemeal restores in SQL Server
In-Memory Optimized database backup and restore in SQL Server
Understanding Backup and Restore operations in SQL Server Docker Containers
Backup and Restore operations with SQL Server 2017 on Docker containers using Azure Data Studio
Interview questions on SQL Server database backups, restores and recovery – Part I
Interview questions on SQL Server database backups, restores and recovery – Part II
Interview questions on SQL Server database backups, restores and recovery – Part III
Interview questions on SQL Server database backups, restores and recovery – Part IV


Prashanth Jayaram
Backup and restore, SQLCMD

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram