How to Create SQL Server Backup Job

All SQL Server database administrators create SQL Server backup job to take database backup of their SQL Servers.
It is of course possible to execute SQL Server backup scripts periodically, or to execute t-sql backup commands from a scheduled SQL Server job, but Microsoft SQL Server 2008 R2 has advanced Maintenance Plan options to manage sql backup tasks for you.

In this SQL Server tutorial, I'ld like to explain how to create SQL Server backup job in order to take sql backup of databases on a SQL Server instance using a SQL Server Maintenance Plan.
I believe sql programmers and database administrators will like to use this SQL Server 2008 backup method.

Please note that the screenshots are from my development computer running Microsoft SQL Server 2008 R2 Developer Edition.

I want to list a list of SQL Server tutorial articles which are demonstrating different methods of SQL Server backup :
SQL Backup - SQL Server Backup Database Tool MS SQL Server Backup Script
SQL Backup Script for All Databases in SQL Server Instance
SQL Server Backup using SQLCmd
T-SQL Backup Database command and SqlCmd Utility

Open SQL Server Management Studio (SSMS).
If it is not visible on the left pane of SSMS, open Object Explorer window.
Connect to SQL Server database instance. (For this sql backup example, I connect to SQL Server 2008 R2 instance).
Open Management node
Right-click on Maintenance Plans node, and open context menu.
Click on Maintenance Plan Wizard menu option.

SQL Server Maintenance Plan Wizard splash screen will be displayed.

Click Next for following step.
Give a descriptive name, and provide a description about the SQL Server backup job here.

sql server backup schedule

The Back Up Database (Full) task allows you to specify the source databases, destination files or tapes, and overwrite options for a full backup.

This SQL Server Maintenance Plan Wizard screen enables SQL backup operator to configure the maintenance task details for full database backup task.
These configuration details include database list for sql backup, backup file folder and backup file name, to verify backup integrity or not, backup compression options, etc.

The Databases dropdown list enables SQL Server administrators to select the sql databases for sql backup job.
The SQL Server backup operator has a few predefined options including :
All databases, to backup all SQL Server databases on the related sql instance.
System databases, this option will take sql backup of SQL Server system databases only.
All user databases, the opposite of system databases option. This option will take SQL Server backup of databases created by users.
These databases options lets the SQL Server Backup operatıor to choose a list of sql databases for backup job.

The checkbox Ignore databases where the state is not online will give the database administrator to decide whether to take sql backups or not for the SQL Server offline databases.

One important issue at this SQL Server backup job step is to configure the SQL Server backup compression option.
There are three options for sql database admins to choose for SQL Server backup compression settings for database backup job :
Use the default server setting,
Compress backup, and
Do not compress backup options.

Default SQL Server backup compression method can be configured for all SQL Server instance from a single point, which sets the server wide default value. Please read the following T-SQL tutorial to set SQL Backup Compression Default Option in MS SQL Server 2008 using sp_configure command.
But it is possible to override default compress backup options for a single SQL Server backup task or for SQL Server backup job.

For further reference in SQL Server backup compression, please refer to following tutorial SQL Server Backup Compression Software - SQL Backup Compression Tools
Also it is possible to manage SQL Server backup compression using third-party sql tools like LiteSpeed Backup SQL Server Backup Compression Tool.