The Basics Of Creating An Automated Database Backup Job

Routinely backing up your database is probably the most important task of a database administrator.  Not to take away from the multitude of other highly important aspects of the job, but having reliable database backups can be the difference between life and death of your data.  However, simply running a backup is rarely enough.  The best practice is to automate this task to run on a schedule of your choosing, though typically, at the very least, you want to do this on a nightly basis.

Below I have outlined the steps necessary to create a database backup script and put it into a SQL Agent job so that it will run on a nightly basis.  It’s worth noting that there are 3 primary types of backups you can perform, Full, Differential and Transaction Log.  A Full backup is just what is sounds like and typically takes the longest to perform.  A Differential backup will only save the data since the last full backup.  Finally, a Transaction Log backup will save all transactions applied to the database and requires a full database backup before one can be made.  Transaction Log backups are typically set to run on frequent intervals in order to minimize data loss should something go horribly wrong.

For the purposes of this overview, I will be creating a full database backup of the DYNAMICS database.

To begin, you’ll need to open SQL Management Studio and connect to your database server.  Once connected, expand the list of databases, right click on the database you want to back up and select Tasks–Back Up…

BackupDatabaseWindow

By default the Backup type should be set to Full and the Name of the backup should have a default name based on the database name and backup type.

Generally the backup destination will show a default destination based on where your instance of SQL Server was installed.  However, its possible that this field will be blank and you will need to set the destination manually.  If this is the case, or you desire to change the default location click the Add… button next to the Destination box.

SelectBackupDestination

From here you can select the default location by either manually entering the path or clicking the ellipses and selecting the path from the directory.  Once the path is chosen you will also need to make sure you have provided a file name within the path.  In the above example I named the file DYNAMICS_F.BAK.  Then click OK to return to the previous window.

From here I could click OK on the Back Up Databases window to kick off the backup and create the DYNAMICS_F.BAK file.  However, I want to take this a step further and create a SQL Agent job to automate this process so that it can be run on a nightly basis.

On the Back Up Database  window click the Scripts button at the top of the screen and select Script Action to Job.

CreateBkupScript

This will create the framework for the SQL Agent Job and automatically create the necessary SQL script for backing up the database based on the information you have already provided.  On the New Job window you will see several pages listed on the left hand side.  We will need to select several of these for review or modification before the job setup is complete.

First, on the General page, you will want to change the job Owner to sa.  This is best practice as all your SQL Agent jobs should have the Owner set to the systems administrator account.

Next select the Steps page. You will see that a single step has already been created.  If you double click the step you will open the Job Step Properties – 1 window to review the details of Step 1.

BackupJobStep1

As you will see, Management Studio has already created the necessary script for backing up the database.  The Advanced page can also be used to instruct the job on what to do should the step fail for any reason.  Since we only have a single step here there is little need to further configure the step.

The final task you will need to complete involves creating the schedule.  If you go back to the New Job window and click on the Schedule page then click the New… button you can begin configuring your backup schedule.

The image below shows a schedule named Backup Schedule that I configured to run daily at 3:15 AM.  Once the schedule has been configured you will see a description at the bottom of the window with a simple explanation of when the job will run.  This should help you make sure you have configured the schedule correctly.

NightlyBackupSchedule

Once you’re schedule has been configured click OK to the New Job window.  Click OK on the New Job window to save the job.

Finally, confirm that job was created by expanding the SQL Server Agent object on the server, then expanding the Jobs folder.  Locate the job you just created.  By default all jobs are enabled once they are created so if you’re not quite ready for the job to be live, simply right-click the job and select disable.  You can also manually start the agent job by right-clicking it and selecting to start at a certain step.  This will allow you to test your job to make sure it will run without any errors.  However, for a backup job I wouldn’t recommend this, especially during hours of operation.

There are quite a few other things you can do when creating an automated SQL job.  One additional step you may want to take is to create a notification so that you will be notified should the job fail.  This can easily be done on the Notifications page of the Job Properties window.  To access the job properties simply right click the job after it has been created and select properties.  You can also configure notifications when you are creating the job. It’s important to note that before you can assign a recipient on a notification you must first create and configure an operator.   Once that has been done it’s just a matter of selecting the appropriate operator from the drop-down on the Notifications page.