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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
-- create a new database CREATE DATABASE ProdSQLShackDemo; GO USE ProdSQLShackDemo; GO -- Set the recovery model of SQLShackDemo to FULL ALTER DATABASE ProdSQLShackDemo SET RECOVERY FULL; GO USE ProdSQLShackDemo; GO -- Create the table SQLShackAuthor CREATE TABLE SQLShackAuthor ( ID int IDENTITY(1,1) PRIMARY KEY, AuthorName nvarchar(100) NOT NULL ); GO --Add records to SQLShackAuthor table INSERT SQLShackAuthor VALUES ('Brain Lockwood'), ('Samir Behara'), ('Ahmad Yaseen'), ('Sifiso W. Ndlovu'), ('Marko Radakovic'), ('Bojan Petrovic'), ('Robert Seles'), ('Marko Zivkovic'), ('Luna Cvetkovic') ; GO |
|
1 2 3 4 |
--Select the records of the table SQLShackAuthor SELECT * 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.
|
1 2 3 4 |
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.
|
1 2 3 |
-- List of filenames within the backup set RESTORE 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.
|
1 2 3 4 5 6 7 |
-- restore the backup to a new database RESTORE 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.
|
1 2 3 |
-- compare the results SELECT * FROM ProdSQLShackDemo..SQLShackAuthor SELECT * 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
---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 @@Servername select * from sys.databases where name='$(DB)' Go :CONNECT $(TGT) SELECT @@Servername select * from sys.databases where name='$(DB)' GO :CONNECT $(SRC) -- Compression Option is set BACKUP DATABASE $(DB) TO DISK = '$(BACKUP_PATH)\$(DB).bak' WITH COPY_ONLY, NOFORMAT, INIT, NAME = '$(DB) Full DB Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 5,COMPRESSION GO ----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 TGT print '*** Restore full backup of DB $(DB) ***' :CONNECT $(TGT) GO USE [master] GO IF EXISTS (select * from sys.databases where name='$(DB)') BEGIN ALTER DATABASE $(DB) SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE $(DB) END RESTORE 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 @@Servername select * from sys.databases where name='$(DB)' GO SELECT @@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.
1Sqlcmd –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
References
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
- 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