Discussing Backup and Restore Automation using SQLCMD and SQL Server agent

Discussing Backup and Restore Automation using SQLCMD and SQL Server agent

Discussing Backup and Restore Automation using SQLCMD and SQL Server agent

SQLShack

SQL Server training Español

Discussing 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') ;GO

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.

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 IV

References

sqlcmd Utility robocopy Back Up and Restore of SQL Server 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

Related posts

SQL Server Database Backup and Restore operations using the Cloud How to perform a page level restore in SQL Server Tail-Log Backup and Restore in SQL Server Encrypted Backup and Restore in AWS RDS SQL Server What is backup and restore in SQL Server disaster recovery? 17,317 Views

Follow us

Popular

SQL Convert Date functions and formats SQL Variables: Basics and usage SQL PARTITION BY Clause overview Different ways to SQL delete duplicate rows from a SQL Table How to UPDATE from a SELECT statement in SQL Server SQL Server functions for converting a String to a Date SELECT INTO TEMP TABLE statement in SQL Server SQL WHILE loop with simple examples How to backup and restore MySQL databases using the mysqldump command CASE statement in SQL Overview of SQL RANK functions Understanding the SQL MERGE statement INSERT INTO SELECT statement overview and examples SQL multiple joins for beginners with examples Understanding the SQL Decimal data type DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key SQL Not Equal Operator introduction and examples SQL CROSS JOIN with examples The Table Variable in SQL Server SQL Server table hints – WITH (NOLOCK) best practices

Trending

SQL Server Transaction Log Backup, Truncate and Shrink Operations Six different methods to copy tables between databases in SQL Server How to implement error handling in SQL Server Working with the SQL Server command line (sqlcmd) Methods to avoid the SQL divide by zero error Query optimization techniques in SQL Server: tips and tricks How to create and configure a linked server in SQL Server Management Studio SQL replace: How to replace ASCII special characters in SQL Server How to identify slow running queries in SQL Server SQL varchar data type deep dive How to implement array-like functionality in SQL Server All about locking in SQL Server SQL Server stored procedures for beginners Database table partitioning in SQL Server How to drop temp tables in SQL Server How to determine free space and file size for SQL Server databases Using PowerShell to split a string into an array KILL SPID command in SQL Server How to install SQL Server Express edition SQL Union overview, usage and examples

Solutions

Read a SQL Server transaction logSQL Server database auditing techniquesHow to recover SQL Server data from accidental UPDATE and DELETE operationsHow to quickly search for SQL database data and objectsSynchronize SQL Server databases in different remote sourcesRecover SQL data from a dropped table without backupsHow to restore specific table(s) from a SQL Server database backupRecover deleted SQL data from transaction logsHow to recover SQL Server data from accidental updates without backupsAutomatically compare and synchronize SQL Server dataOpen LDF file and view LDF file contentQuickly convert SQL code to language-specific client codeHow to recover a single table from a SQL Server database backupRecover data lost due to a TRUNCATE operation without backupsHow to recover SQL Server data from accidental DELETE, TRUNCATE and DROP operationsReverting your SQL Server database back to a specific point in timeHow to create SSIS package documentationMigrate a SQL Server database to a newer version of SQL ServerHow to restore a SQL Server database backup to an older version of SQL Server

Categories and tips

►Auditing and compliance (50) Auditing (40) Data classification (1) Data masking (9) Azure (295) Azure Data Studio (46) Backup and restore (108) ►Business Intelligence (482) Analysis Services (SSAS) (47) Biml (10) Data Mining (14) Data Quality Services (4) Data Tools (SSDT) (13) Data Warehouse (16) Excel (20) General (39) Integration Services (SSIS) (125) Master Data Services (6) OLAP cube (15) PowerBI (95) Reporting Services (SSRS) (67) Data science (21) ►Database design (233) Clustering (16) Common Table Expressions (CTE) (11) Concurrency (1) Constraints (8) Data types (11) FILESTREAM (22) General database design (104) Partitioning (13) Relationships and dependencies (12) Temporal tables (12) Views (16) ►Database development (418) Comparison (4) Continuous delivery (CD) (5) Continuous integration (CI) (11) Development (146) Functions (106) Hyper-V (1) Search (10) Source Control (15) SQL unit testing (23) Stored procedures (34) String Concatenation (2) Synonyms (1) Team Explorer (2) Testing (35) Visual Studio (14) DBAtools (35) DevOps (23) DevSecOps (2) Documentation (22) ETL (76) ►Features (213) Adaptive query processing (11) Bulk insert (16) Database mail (10) DBCC (7) Experimentation Assistant (DEA) (3) High Availability (36) Query store (10) Replication (40) Transaction log (59) Transparent Data Encryption (TDE) (21) Importing, exporting (51) Installation, setup and configuration (121) Jobs (42) ▼Languages and coding (686) Cursors (9) DDL (9) DML (6) JSON (17) PowerShell (77) Python (37) R (16) SQL commands (196) SQLCMD (7) String functions (21) T-SQL (275) XML (15) Lists (12) Machine learning (37) Maintenance (99) Migration (50) Miscellaneous (1) ►Performance tuning (869) Alerting (8) Always On Availability Groups (82) Buffer Pool Extension (BPE) (9) Columnstore index (9) Deadlocks (16) Execution plans (125) In-Memory OLTP (22) Indexes (79) Latches (5) Locking (10) Monitoring (100) Performance (196) Performance counters (28) Performance Testing (9) Query analysis (121) Reports (20) SSAS monitoring (3) SSIS monitoring (10) SSRS monitoring (4) Wait types (11) ►Professional development (68) Professional development (27) Project management (9) SQL interview questions (32) Recovery (33) Security (84) Server management (24) SQL Azure (271) SQL Server Management Studio (SSMS) (90) SQL Server on Linux (21) ►SQL Server versions (177) SQL Server 2012 (6) SQL Server 2016 (63) SQL Server 2017 (49) SQL Server 2019 (57) SQL Server 2022 (2) ►Technologies (334) AWS (45) AWS RDS (56) Azure Cosmos DB (28) Containers (12) Docker (9) Graph database (13) Kerberos (2) Kubernetes (1) Linux (44) LocalDB (2) MySQL (49) Oracle (10) PolyBase (10) PostgreSQL (36) SharePoint (4) Ubuntu (13) Uncategorized (4) Utilities (21) Helpers and best practices BI performance counters SQL code smells rules SQL Server wait types © 2022 Quest Software Inc. ALL RIGHTS RESERVED. GDPR Terms of Use Privacy
Share:
0 comments

Comments (0)

Leave a Comment

Minimum 10 characters required

* All fields are required. Comments are moderated before appearing.

No comments yet. Be the first to comment!