Scheduled Database Backup & Restore

Not only is a scheduled database backup important, but so is the successful restore of the database. Too often organisations are lulled into a false sense of security because they have backups - only to have the backups not work when needed to restore data from. This guide shows how to use the SQL Server Agent to backup and restore a database based on a schedule, and notify users of success or failure.

1 Create a Backup Job
2 Create a Restore Job
3 Related articles

Create a Backup Job

Follow the steps below to create a SQL Server Agent job that backs up a database:

In Microsoft SQL Server Management Studio, right-click on the "SQL Server Agent" node and choose "New → Job..."
The "New Job" dialog will be displayed. In The "General" section enter details such as name and description.
Go to the "Steps" section, and click on the "New..." button to add a step to the new job.
In the "New Job Step" window that appears, enter step name, and the command text.

The command text is a T-SQL command that perfoms a full backup of the database named "JiwaDemo" to the location "D:\SQLBackups\" with a filename of "JiwaDemo.Bak". The command text is given here for copy-pasting:

BACKUP DATABASE [JiwaDemo]
TO DISK = N'D:\SQLBackups\JiwaDemo.Bak'
WITH INIT, SKIP, CHECKSUM
GO
After clicking the "OK" button to close the "New Job Step" window from step 4, the "New Job" window, "Steps" section will now look like this:

See that our job now has a step 1. Subsequent steps can be added if desired. For our purposes we only need the one step performed (we will be creating the database Restore as it's own separate job later in this article).
Move on to the "Schedules" section and click on the "New..." button to create a new schedule. This, of course, will dictate when our job will run, and if it repeats, etc.
After clicking on the "New..." button in step 6 above, the "New Job Schedule" window is shown. Give the schedule a name and choose when to run the job, be it monthly, weekly, daily, etc. We will run our backup job every morning at 3am. It's an ongoing job so no end date is defined.
After defining the schedule and clicking the "OK" button in step 7, the "New Job" window will look like this:
Now proceed straight to the "Notifications" tab. This is where e-mail recipients can be defined for when a job succeeds or fails. For emailing to work, Database Mail must be configured on the server (see the how-to article on Configuring SQL Server Database Mail for more information). Further, one or more SQL Server "Operators" must be configured, such that they can be selected to receive the email (see the how-to article on Adding SQL Operators for more information). Remember that a SQL Operator may not represent a single person, but a group of people - as such the email address defined for the SQL Operator would be a mailing group rather than an individuals email address. That way, many people in the organisation will be alerted when the job succeeds or fails.
After completing the "Notifications" section in step 9 above, click the "OK" button to complete the new job definition. The job will now appear under the "Jobs" node of the "SQL Server Agent" node of the SQL Server, thus:

Create a Restore Job

Follow the steps below to create a SQL Server Agent job that restores a database from a backup file. The restore operation will create a new database if required - if it already exists it will be overwritten.

In Microsoft SQL Server Management Studio, right-click on the "SQL Server Agent" node and choose "New → Job..."
The "New Job" dialog will be displayed. In The "General" section enter details such as name and description.
Go to the "Steps" section, and click on the "New..." button to add a step to the new job.
In the "New Job Step" window that appears, enter step name, and the command text.

The command text is a T-SQL command that performs a restore from the file at "D:\SQLBackups\JiwaDemo.Bak" to a database named "JiwaDemo_QA". If a database named "JiwaDemo_QA" does not exist it is created, if it does exist, it is overwritten. The command text is given here for copy-pasting:

RESTORE DATABASE JiwaDemo_QA
FROM DISK = N'D:\SQLBackups\JiwaDemo.Bak'
WITH MOVE 'JiwaDemo' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\JiwaDemo_QA.mdf',
MOVE 'JiwaDemo_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\JiwaDemo_QA_log.ldf';
GO
After clicking the "OK" button to close the "New Job Step" window from step 4, the "New Job" window, "Steps" section will now look like this:
Now proceed straight to the "Notifications" tab. We will not be executing this job on a schedule - rather we will call it directly from our backup job created in the first part of this how-to guide. For emailing to work, Database Mail must be configured on the server (see the how-to article on Configuring SQL Server Database Mail for more information). Further, one or more SQL Server "Operators" must be configured, such that they can be selected to receive the email (see the how-to article on Adding SQL Operators for more information). Remember that a SQL Operator may not represent a single person, but a group of people - as such the email address defined for the SQL Operator would be a mailing group rather than an individuals email address. That way, many people in the organisation will be alerted when the job succeeds or fails.
After completing the "Notifications" section in step 9 above, click the "OK" button to complete the new job definition. The job will now appear under the "Jobs" node of the "SQL Server Agent" node of the SQL Server, thus:
Now it is time to go back and edit the "Backup JiwaDemo Database" job - the "Backup JiwaDemo Database" job will have a step added to it to call the newly created "Restore JiwaDemo Database" job. Right-click on the "Backup JiwaDemo Database" entry under the "SQL Server Agent" node of the SQL Server and choose "Properties":
The Job Properties window should now appear:
Click on the "Steps" entry on the left to access the list of steps that make up the "Backup JiwaDemo Database" job, then select step 1 and click on the "Edit" button to edit the step.
This will bring up the "Job Step Properties" window - click on the "Advanced" section on the left:
Change the "On success action:" from "Quite the job reporting success" to "Go to the next step".
Click on the "OK" button to return to the "Steps" section of the job. Click on the "New..." button to add a new step:
In the "New Job Step" window that appears, enter step name, and the command text.

The command text calls a job called "Restore JiwaDemo Database". Here is the text for copy-pasting into your command text box:
EXEC msdb.dbo.sp_start_job @job_name = 'Restore JiwaDemo Database';
GO
Click the "OK" button to close the "New Job Step" window. There are now 2 steps defined for the "Backup JiwaDemo Database" - the first step performs the actual backup, and the second step calls the "Restore JiwaDemo Database" job.
After completing step 12 above, click the "OK" button to complete the "Backup JiwaDemo Database" job modification. A prompt will appear noting that the last step will have it's "On Success" action changed from "Goto Next Step" to "Quit with Success" - click "Yes" to accept this change.
We can test by right-clicking on the "Backup JiwaDemo Database" entry under the "SQL Server Agent" node of the SQL Server and choosing "Start Job at Step...":
Ensure that step 1 is highlighted, and then click the "Start" button on the "Start Job" dialog window:
A progress dialog will appear. Double check that everything executed OK by looking at the date time stamp of the backup file in "D:\SQLBackups\JiwaDemo.Bak" (or whatever path you used in step 4 of creating the backup job). To double check that the database has been restored successfully, look at the restore history for the target database name (defined in step 4 of the creating the restore job) by running the t-sql script given below:

WITH LastRestores AS
(
SELECT
DatabaseName = [d].[name] ,
[d].[create_date] ,
[d].[compatibility_level] ,
[d].[collation_name] ,
r.*,
RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1