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: