Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
1234 --Select the records of the table SQLShackAuthorSELECT * FROM SQLShackAuthor; GO
The backup database command is used with the FORMAT clause. The format option overwrites any existing backups and creates a new backup set. 1234 BACKUP DATABASE ProdSQLShackDemo TO DISK = 'F:\PowerSQL\ProdSQLShackDemo.BAK' WITH FORMAT;GO 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. 123 -- List of filenames within the backup setRESTORE FILELISTONLY FROM DISK = 'F:\PowerSQL\ProdSQLShackDemo.bak';
In this section, we will see how to restore the database with a different name: TestSQLShackDemo. Use the F:\PowerSQL\ProdSQLShackDemo.bak as a reference backup file. The MOVE clause is used to move the files to a different file location. The keyword RECOVERY is used, since we don’t have any further backups to restore. 1234567 -- restore the backup to a new databaseRESTORE DATABASE TestSQLShackDemo FROM DISK = 'F:\PowerSQL\ProdSQLShackDemo.BAK' WITH MOVE 'ProdSQLShackDemo' TO 'F:\PowerSQL\TestSQLShackDemo.BAK', MOVE 'ProdSQLShackDemo_log' TO 'F:\PowerSQL\TestSQLShackDemo_log.BAK', RECOVERY;GO We can see that the new TestSQLShackDemo has been created. To verify the restoration process, select everything out of these two tables. 123 -- compare the resultsSELECT * FROM ProdSQLShackDemo..SQLShackAuthorSELECT * FROM TestSQLShackDemo..SQLShackAuthor
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. Declare the variable Source production database: DB Source server: SRC Target server: TGT Source database backup path: BACKUP_PATH Target database restore path: RESTORE_PATH Source database data file name: DATAFILENAME Source database log filename: LOGFILENAME Target server data file location: RESTORE_DATA_PATH Target Server log file location: RESTORE_LOG_PATH Connect to source and target to check the existence of the database Backup the database Use the robocopy utility for the copy operation. This way we quickly transfer data across the network. 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. Restore the database Validate the output 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364 ---define variables and its values :setvar DB ProdSQLShackDemo:setvar SRC HQDBSP18:setvar TGT HQDBt01:setvar BACKUP_PATH f:\PowerSQL:setvar RESTORE_PATH f:\PowerSQL:setvar DATAFILENAME ProdSQLShackDemo:setvar LOGFILENAME ProdSQLShackDemo_log:setvar RESTORE_DATA_PATH "f:\PowerSQL":setvar RESTORE_LOG_PATH "f:\PowerSQL":setvar COPYPATH f$\PowerSQL:setvar Timeout 100 ---Precheck for an existence of DB :CONNECT $(SRC)SELECT @@Servernameselect * from sys.databases where name='$(DB)'Go:CONNECT $(TGT)SELECT @@Servernameselect * from sys.databases where name='$(DB)'GO :CONNECT $(SRC)-- Compression Option is setBACKUP DATABASE $(DB) TO DISK = '$(BACKUP_PATH)\$(DB).bak'WITH COPY_ONLY, NOFORMAT, INIT, NAME = '$(DB) Full DB Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 5,COMPRESSIONGO----2. Copy the files from SRC to TGT , Refer below link for more --information print '*** Copy DB $(DB) from SRC server $(SRC) to TGT server $(TGT) ***'!!ROBOCOPY $(BACKUP_PATH)\ \\$(TGT)\$(COPYPATH) $(DB).*GO---–3. Restore DB to TGTprint '*** Restore full backup of DB $(DB) ***':CONNECT $(TGT)GOUSE [master]GOIF EXISTS (select * from sys.databases where name='$(DB)')BEGINALTER DATABASE $(DB) SET SINGLE_USER WITH ROLLBACK IMMEDIATEDROP DATABASE $(DB)ENDRESTORE DATABASE $(DB) FROM disk = '$(RESTORE_PATH)\$(DB).bak' WITH RECOVERY, NOUNLOAD, STATS = 10,REPLACE, MOVE '$(DATAFILENAME)' TO '$(RESTORE_DATA_PATH)\$(DATAFILENAME).mdf', MOVE '$(LOGFILENAME)'TO '$(RESTORE_DATA_PATH)\$(LOGFILENAME).ldf'GO ---Post Check for an existence of DB on both SRC and TGT :CONNECT $(SRC)SELECT @@Servernameselect * from sys.databases where name='$(DB)'GOSELECT @@Servername:CONNECT $(TGT)select * from sys.databases where name='$(DB)' 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 Save the sqlcmd script to a file BackupandRestoreAutomation.sql To test that sqlcmd is working, execute the SQL Script file from the command prompt. After successful execution, proceed with the following steps Create a new job on the SQL server agent. Browse object explorer, expand the SQL Server agent folder. Select the jobs folder and right click and create a New Job… In the general properties, enter the name of the job. Let’s call it BackupandRestoreAutomation. Switch the selection on the left from general to steps In the general tab of the Job Steps, enter the step name; in this case, the step name is “Execute the sqlcmd script” Choose the type as Operating system (CmdExec) 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. 1 Sqlcmd –i f:\powersql\BackupandRestoreAutomation.sql
Click on the OK button. Next, switch the selection on the left from steps to schedules To add a schedule, click the New button The name of the schedule is BackupAndRestoreSchedule. Choose the frequency and best time to run the job as per your requirements. Click OK to save the schedule
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.
Author Recent Posts Prashanth JayaramI’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 Latest posts by Prashanth Jayaram (see all) Stairway to SQL essentials - April 7, 2021 A quick overview of database audit in SQL - January 28, 2021 How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021
SQLShack
SQL Server training EspañolDiscussing Backup and Restore Automation using SQLCMD and SQL Server agent
May 3, 2018 by Prashanth Jayaram 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. Restore the database to the same server with different name Restore the database to different server using sqlcmd and Robocopy utility 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. 123456789101112131415161718192021222324252627282930 -- create a new databaseCREATE DATABASE ProdSQLShackDemo;GOUSE ProdSQLShackDemo;GO-- Set the recovery model of SQLShackDemo to FULLALTER DATABASE ProdSQLShackDemo SET RECOVERY FULL;GO USE ProdSQLShackDemo;GO-- Create the table SQLShackAuthorCREATE TABLE SQLShackAuthor ( ID int IDENTITY(1,1) PRIMARY KEY, AuthorName nvarchar(100) NOT NULL);GO--Add records to SQLShackAuthor tableINSERT SQLShackAuthor VALUES ('Brain Lockwood'), ('Samir Behara'), ('Ahmad Yaseen'), ('Sifiso W. Ndlovu'), ('Marko Radakovic'), ('Bojan Petrovic'), ('Robert Seles'), ('Marko Zivkovic'), ('Luna Cvetkovic') ;GO1234 --Select the records of the table SQLShackAuthorSELECT * FROM SQLShackAuthor; GO
The backup database command is used with the FORMAT clause. The format option overwrites any existing backups and creates a new backup set. 1234 BACKUP DATABASE ProdSQLShackDemo TO DISK = 'F:\PowerSQL\ProdSQLShackDemo.BAK' WITH FORMAT;GO 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. 123 -- List of filenames within the backup setRESTORE FILELISTONLY FROM DISK = 'F:\PowerSQL\ProdSQLShackDemo.bak';
In this section, we will see how to restore the database with a different name: TestSQLShackDemo. Use the F:\PowerSQL\ProdSQLShackDemo.bak as a reference backup file. The MOVE clause is used to move the files to a different file location. The keyword RECOVERY is used, since we don’t have any further backups to restore. 1234567 -- restore the backup to a new databaseRESTORE DATABASE TestSQLShackDemo FROM DISK = 'F:\PowerSQL\ProdSQLShackDemo.BAK' WITH MOVE 'ProdSQLShackDemo' TO 'F:\PowerSQL\TestSQLShackDemo.BAK', MOVE 'ProdSQLShackDemo_log' TO 'F:\PowerSQL\TestSQLShackDemo_log.BAK', RECOVERY;GO We can see that the new TestSQLShackDemo has been created. To verify the restoration process, select everything out of these two tables. 123 -- compare the resultsSELECT * FROM ProdSQLShackDemo..SQLShackAuthorSELECT * FROM TestSQLShackDemo..SQLShackAuthor
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. Declare the variable Source production database: DB Source server: SRC Target server: TGT Source database backup path: BACKUP_PATH Target database restore path: RESTORE_PATH Source database data file name: DATAFILENAME Source database log filename: LOGFILENAME Target server data file location: RESTORE_DATA_PATH Target Server log file location: RESTORE_LOG_PATH Connect to source and target to check the existence of the database Backup the database Use the robocopy utility for the copy operation. This way we quickly transfer data across the network. 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. Restore the database Validate the output 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364 ---define variables and its values :setvar DB ProdSQLShackDemo:setvar SRC HQDBSP18:setvar TGT HQDBt01:setvar BACKUP_PATH f:\PowerSQL:setvar RESTORE_PATH f:\PowerSQL:setvar DATAFILENAME ProdSQLShackDemo:setvar LOGFILENAME ProdSQLShackDemo_log:setvar RESTORE_DATA_PATH "f:\PowerSQL":setvar RESTORE_LOG_PATH "f:\PowerSQL":setvar COPYPATH f$\PowerSQL:setvar Timeout 100 ---Precheck for an existence of DB :CONNECT $(SRC)SELECT @@Servernameselect * from sys.databases where name='$(DB)'Go:CONNECT $(TGT)SELECT @@Servernameselect * from sys.databases where name='$(DB)'GO :CONNECT $(SRC)-- Compression Option is setBACKUP DATABASE $(DB) TO DISK = '$(BACKUP_PATH)\$(DB).bak'WITH COPY_ONLY, NOFORMAT, INIT, NAME = '$(DB) Full DB Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 5,COMPRESSIONGO----2. Copy the files from SRC to TGT , Refer below link for more --information print '*** Copy DB $(DB) from SRC server $(SRC) to TGT server $(TGT) ***'!!ROBOCOPY $(BACKUP_PATH)\ \\$(TGT)\$(COPYPATH) $(DB).*GO---–3. Restore DB to TGTprint '*** Restore full backup of DB $(DB) ***':CONNECT $(TGT)GOUSE [master]GOIF EXISTS (select * from sys.databases where name='$(DB)')BEGINALTER DATABASE $(DB) SET SINGLE_USER WITH ROLLBACK IMMEDIATEDROP DATABASE $(DB)ENDRESTORE DATABASE $(DB) FROM disk = '$(RESTORE_PATH)\$(DB).bak' WITH RECOVERY, NOUNLOAD, STATS = 10,REPLACE, MOVE '$(DATAFILENAME)' TO '$(RESTORE_DATA_PATH)\$(DATAFILENAME).mdf', MOVE '$(LOGFILENAME)'TO '$(RESTORE_DATA_PATH)\$(LOGFILENAME).ldf'GO ---Post Check for an existence of DB on both SRC and TGT :CONNECT $(SRC)SELECT @@Servernameselect * from sys.databases where name='$(DB)'GOSELECT @@Servername:CONNECT $(TGT)select * from sys.databases where name='$(DB)' 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 Save the sqlcmd script to a file BackupandRestoreAutomation.sql To test that sqlcmd is working, execute the SQL Script file from the command prompt. After successful execution, proceed with the following steps Create a new job on the SQL server agent. Browse object explorer, expand the SQL Server agent folder. Select the jobs folder and right click and create a New Job… In the general properties, enter the name of the job. Let’s call it BackupandRestoreAutomation. Switch the selection on the left from general to steps In the general tab of the Job Steps, enter the step name; in this case, the step name is “Execute the sqlcmd script” Choose the type as Operating system (CmdExec) 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. 1 Sqlcmd –i f:\powersql\BackupandRestoreAutomation.sql
Click on the OK button. Next, switch the selection on the left from steps to schedules To add a schedule, click the New button The name of the schedule is BackupAndRestoreSchedule. Choose the frequency and best time to run the job as per your requirements. Click OK to save the schedule
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: Using SQLCMD with the source and target being on the same instance on the same server. 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 IVReferences
sqlcmd Utility robocopy Back Up and Restore of SQL Server DatabasesAuthor Recent Posts Prashanth JayaramI’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 Latest posts by Prashanth Jayaram (see all) Stairway to SQL essentials - April 7, 2021 A quick overview of database audit in SQL - January 28, 2021 How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021