SQL Server Essentials: How to Create a Simple Backup Job in SQL Server

Creating backups is one of the most important duties of an SQL Server database administrator, and there are a number of different ways to back up your most important databases. In this article, I’ll explain how to create a simple SQL Server backup job using SQL Server Management Studio (SSMS).

The different ways to create SQL Server backup jobs

Database backups are different from regular system or storage backups as databases are not just simple files. They have internal objects like users, permissions, indexes, views, and transactions.

While the end result of a database backup is a backup file that can be restored, the actual backup process needs to be database-aware in order to maintain complete consistency after a restore operation.

There are three main types of SQL Server backups:

• Full database backup: A full database backup is created by the BACKUP DATABASE statement, as we’ll see below. When you restore this backup, the entire database is restored to the state when the backup was taken.
• Transaction log backup: This backup type can only be created after having performed a full database backup. As you can guess, it only backs up the transaction log, which is backed up from the last successfully executed log backup at the current end of the log. Log backups can also be restored to a specific time or transaction.
• Differential backup: This backup type only includes the portions of the database or files that changed since the last full backup. It usually takes up less space than a full backup. Using this option also eliminates the need to restore all individual log backups performed since the last full backup.

The 3 types of SQL Server database recovery models

The type of backup and restore that you can perform depends on the SQL Server database recovery model you’ll be using. The recovery model is a database configuration option that determines the type of backup that you can perform. There are three different recovery models you can use:

• Full: This recovery model supports all restore operations including full database backups, differential database backups, and file-level backups. It also supports transaction log point-in-time restores, page restores, and file restores.
• Simple: This recovery model supports full, differential, and file-level backups. Transaction log backups are not supported.
• Bulk-Logged: This recovery model works like the full recovery model, except that certain bulk operations are minimally logged and it’s not possible to restore specific point-in-time data.

The different ways to back up an SQL Server database

There are a number of different ways you can backup a SQL Server database, here are the main ones you should know about:

T-SQL BACKUP

The T-SQL BACKUP command is core to most of the other backup mechanisms. When run from the Query Editor, the command will backup up a single database. This option can make an effective backup strategy when combined with scripts or the SQL Server Agent, as you will see later in this article.

SQL Server Management Studio (SSMS) Backup Wizard

The SSMS Backup Wizard is best for one-off ad hoc backups. However, it isn’t scalable and not well suited for routine data protection.

Azure Data Studio

Likewise, Azure Data Studio provides an interactive database backup wizard that enables you to perform ad hoc database backups. However, as the SSMS option, this method is not scalable or suited to being used for routine data protection.

SQL Server Maintenance Plans

SQL Server maintenance plans provide a way to automate database backups, and they can be a good option for smaller businesses. These maintenance plans can be created fairly easily using the SSMS GUI, but they are not as flexible as some of the other options.

PowerShell

You can also perform SQL Server database backups using the PowerShell Backup-SqlDatabase cmdlet. This cmdlet has essentially the same capabilities as the native T-SQL BACKUP statement, and it includes the option to backup transaction logs.

Overall, PowerShell backups can be a good option if you want to include SQL Server database backups as a part of some external backup script or process.

Ola Hellengren scripts

Ola Hellengren is a Data Platform MVP who created an award-Winning SQL Server solution for backups and maintenance. These scripts are not officially a part of SQL Server, and they are not provided by Microsoft. However, many businesses have incorporated them into their SQL Server operations routines to perform tasks such as backups, integrity checks, index and statistics maintenance. For SQL Server backups, they create SQL Server Agent jobs.

SQL Server Agent jobs

The SQL Server Agent is a popular and reliable way to run one or more database backups. The SQL Server Agent works as an automated job scheduler, and it can run many different types of routine operations including backups automatically.

Creating a SQL Server Agent backup job with SSMS

To schedule an SQL Server database backup, you first need to create an SQL Server Agent job, and then add the T-SQL BACKUP command to that job.

Starting the SQL Server Agent

To create a backup job using the SQL Server Agent, you first need to open SQL Server Management Studio and then navigate to the SQL Server Agent node, as you can see in the following figure.

If you have not used the SQL Server Agent before, it might not have started already. You can start it by right-clicking on the node and then selecting Start from the context menu.

Creating a new backup job

To create a new backup job, right-click the SQL Server Agent node and select New > Job from the context menu.

This will display the New Job dialog shown in the following figure. Here, you can provide a name and description for your new SQL Server Agent job.

• The Name prompt allows you to name the job. In this case, the job name is ‘AdventureWorksBackup’.
• The Owner prompt defaults to the current user, but you can change it to a different user.
• The Category prompt is mainly used as a description that tags the job
• The Description field allows you to key in several lines of descriptive text about the job.

After you’ve filled out this page, click OK to add the job to the SQL Server Agent. This will add the ‘AdventureWorksBackup’ job under the Jobs note in Object Explorer.

Adding the T-SQL BACKUP command to your job

Next, to add the T-SQL BACKUP command to your job, right-click the AdventureWorksBackup job node and then select Properties from the context menu.

This will launch the Job Properties dialog where you can specify the job’s commands, schedules, notifications, and more:

• To add the T-SQL BACKUP statement, click on the Steps node in the left panel to display the list of job steps. For a new job, it will be blank because you haven’t created any steps yet. 
• To add a backup step, click on New.

This will display the New Job Step dialog which allows you to create a new job step.

• First, give the job step a name. Here, we used ‘Backup’.
• Select the Type: There are several step types to choose from, and the default is Transact-SQL script (T-SQL).
• Next, supply the T-SQL BACKUP command in the Command pane. You can type it manually, but it’s usually better if you test it by running the command from inside a Query Editor window and then cut-and-paste the command into the Command pane.
• To back up the AdventureWorks2019 SQL Server database, you can use the following command:

BACKUP DATABASE AdventureWorks2019 TO DISK = 'c:\backups\AdventureWorks2019.bak'

This command creates a full SQL Server database backup of the AdventureWorks2019 user database to the disk file AdventureWorks2019.bak that’s located in the c:\backups folder. This example uses a disk device as the backup target, but you could also use tape or other backup media.

After entering your command, click OK to return to the Job Properties page where your new job step will be listed. At this point, you can either add additional commands to the command pane or add additional job steps. For instance, you could add additional steps for all the different databases you want to back up.

Scheduling your job

Next, to schedule your job, click on Schedules in the left pane, which will display the Schedule list dialog shown below. Here, click New to open the New Job Schedule dialog.

Now, give the new schedule a name and then choose the frequency and interval that’s right for your backup job. Click OK when you’re done.

More options to create SQL Server backup jobs

In this tutorial, I detailed the different types of SQL Server backups that are available. I also explained how the SQL Server Agent can be used to schedule one or more jobs to run automatically.

Many businesses do periodic full backups along with more frequent differential backups and even more frequent transaction log backups. While the example I used in this article was a simple backup type, you also have the option of using encryption, compression, mirroring, and more.