SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014



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-for-sql-backup-job

SQL Server Maintenance Plan Wizard splash screen will be displayed.

sql-server-maintenance-plan-wizard-for-sql-backup

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

sql-server-maintenance-plan-properties

sql server backup schedule

sql-server-backup-job-schedule-properties

sql-backup-database-in-sql-server-maintenance-task

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

sqlserver-maintenance-plan-task-order

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.

define-back-up-database-task-sqlserver2008r2

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.

select-sql-databases-for-sql-backup-job-task

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.

sql-server-database-backup-to-disk-compression

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.

tsql-backup-job-maintenance-plan-report

sql-server-2008-r2-maintenance-plan-summary

create-sql-server-maintenance-plan

sql-server-management-node-maintenance-plans-sql-backup-job






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems