SQL Backup - SQL Server Backup Database Tool in SQL Server Management Studio

What is SQL Server Backup?

SQL Server backup is copy of your data in your database which can be used to restore your data in case of a failure in your production environment or in your data platform.
SQL Server Backup Administrators are responsible from regularly taking sql backups of databases in SQL Server instances.
Database backups can be stored on disks or on backup tapes according to the medium that is accepted in backup and recovery scenarios.

SQL Backup Tool in SQL Server Management Studio (SSMS)

SQL Server administrators can take full database backup using Microsoft SQL Server Management Studio in SQL Server 2005 and later versions including (SQL Server 2008 and MSSQL Server 2008 R2).
SQL Server DBA 's can use the previous SQL Server Management Tool, SQL Server Enterprise Manager for SQL Server 2000 for example.
It is possible to take SQL backups using t-sql commands and a sql backup script of course.
And even you can develop a MS SQL backup script for all databases as illustrated at SQL Backup Script for All Databases in SQL Server Instance.
But the cover of this sql tutorial is to show how to take SQL Server backup using SQL Server Management Studio step by step with screenshots.

I've installed MS SQL Server 2008 R2 Developer Edition on my laptop.
And I'll be showing sql administrators and sql developers how to take sql backup using my SQL2008 R2 Developer Edition computer.

How to Backup SQL Server

Let's start with openning SQL Server Management Studio and connecting to target SQL Server instance.

Open SQL Server Management Studio.
Connect to SQL Server instance where the database to backup resides.
After you sql administrator or sql developer is connected to SQL Server 2008 R2 instance using a valid sql login or Windows user which is authenticated to take sql backup, list sql databases by clicking on the Databases node within the Object Explorer.
If Object Explorer windows is not seen in the SSMS, press F8 short-cut or goto View on main Menu, then select Object Explorer menu item.
This menu option will display Object Explorer windows within the SQL Server Management Studio.

Right click on the sql database which you want to take sql backup of.
This action will display the context menu.
Highlight Tasks submenu item.
You will see "Back up..." subitem display, highlight and select "Back up..." task from the list.

SQL Server Backup Database Wizard

Backup Database Wizard or backup sql database dialog screen will start.

The Back up wizard consists of two tabs or two pages : General and Options.
Let's continue with configuring General tab options.
In this tab, there are three main sections to be configred for a successfull sql database backup action:
Source,
Backup set, and
Destination.

First, in the Database dropdown list, select database which you want to take sql backup.

According to the sql database you have selected the Recovery model of the database is displayed as read only.
The possible recovery models for a database can be listed as Simple, Full or Bulk_Logged.

SQL Server Database Backup Type

After target database for backup is selected, it is time to select the database backup type.
SQL backup types are Full, Differential and Transaction Log database backup types.
It is notable that under Simple Recovery Model, Transaction log backup can not be taken.
For example, if you try to take backup of Model database which has Full Recovery Model, you will see that Transaction Log backup type is listed and available for process.

Copy-Only Backup

An other interesting option is Copy-Only Backup indicator or copy-only backup checkbox.
Copy-Only backups are an enhancement introduced with Microsoft SQL Server 2005 but you may not see this indicator on a SQL Server Management Studio 2005 screen.
Copy-Only backup option is supported with SQL Server 2008 on SQL Server Management Studio.
Copy-Only backups does not affect the sequence of sql backups you have scheduled and configured for your data recovery scenario.
For example, your company SQL Server backup administrator might configure to take full backups once a week and additional differential backups daily.
Then taking sql backup with Copy-Only option will not affect this backup sequence.
In fact, copy-only sql backups are special backup cases irrelevent to scheduled tasks and recovery models.
For more information please refer to BOL (Books OnLine) Copy-Only Backups topic.

Backup Component

Another option that can be specified in this section is the Backup component, which can be either Database or Files and filegroups.
The database backup component sets the sql backup process for the entire SQL Server database.
On the other hand, sql administrators or DBAs can specify database files or database file groups for SQL backup task.
Default option is entire database so it is logical to continue with this option if your requirements are not so advanced to take backups of different datafiles or filegroups forming the sql database.

Backup Set

Following section on the SQL Backup wizard screen is the Backup Set configuration section.
In this section of the Backup task wizard screen, administrators can define backup set properties like name and description of the set.
Besides DBAs can define backup set expiration date in days or by specifying a specific date.

SQL Server Database Backup Destination

SQL Backup Destination is an important parameter to be configured for successfull sql backups.
Here IT professionals define the backup device for the sql database back-up operation.
This means defining a logical or a physical backup device for the related sql task.

It is possible to define the destination as a backup tape or as a physical disk.
If you choose SQL backup destionation as Disk option, you can set a file folder and a name for the sql backup file which is on the local server or on a file share which is accessible by the back up user account.

The default back up folder is "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\" by default.
This default backup folder is a sub-folder under the SQL Server 2008 R2 installation folder, just as other SQL Server versions.
It is possible to define an other file folder to store SQL Server backups.
That is a best practice, to keep sql backups on different physical devices to keep the data safety of your backups.

In order to specify a different disk destination for SQL Server database backup file, first click on Remove button to delete the existing data folder for your data backup.
Then click Add button in order to select a destination folder for sql backup file on the SQL Server machine.
This is very important, when you are selecting a folder using the below "Locate Database Files" dialog screen, these file folder structure belongs to the server where the SQL Server 2005 or MS SQL Server 2008 instance is installed.

Select the target folder to store database back up. Your selection will be displayed in the "Selected path" text area.
Then define a backup file name to your sql backup file.
In the below screenshot, it is seen that my data backup file is named as "AdventureWorks2008R2".
It is common among SQL Server developers and administrators to name backup file with an extension ".bak"
The file extension ".bak" is a common indicator for backup files. So it is best practice to name sql backup file as "AdventureWorks2008R2.bak"

After target path and data backup file name is defined, click on OK button.

The selection will be seen again on the above screen within the wizard.
After you click OK button, sql developers or database administrators will see the same path and sql database backup file name in the destination section of the wizard main screen.

In order to have more control over sql backup process navigate to the Options page or go to Options tab.

In the below screenshot SQL Server administrators or backup administrators will see my preferred sql backup options.
You will see that I chosed the "Overwrite all existing backup sets".
In fact if you name your SQL Server backup files each time, you can define different backup files names.
This time you do not actually need to think on this option, Append or Overwrite both will have the same result.

By the way it is very common to name the MS SQL Server database backup files with dbname, date and hour-minute combinations.
If it is required to give an example, I could name the sql backup file as "AdventureWorks2008R2-201004152030.bak"

Actually backup sets does not force you to give different names for your sql backup tasks each time.
Each database backup is placed in a single sql backup set. And within sql backup set, there may be many sql backups of a database taken in different times.
It is important that if Backup Sets are preferred to be used, the "Append to the existing backup set" option should be selected.
Of course there may be times when backup managers want to delete all backups in a backup set.
Then the solution is selecting Overwrite option once and continue with Append option for the following backup processes.

SQL Backup Reliability

Data backup files are taken in order to keep safety of your operations.
In case of a disaster, I mean in case your data in sql databases are corrupt then you have a copy of your data somewhere else.
That is very nice. Of course if your copy of data, sql backup files are reliable.
This means you should guarantee that if you have to restore sql database from sql backup file, it should be successfull.
None of the SQL Server database administrators would like to be in a situation where restore database operation fails.

I prefer to take sql backups with the following reliability options are marked :
Verify backup when finished
Perform checksum before writing to media
Both these options might cause a workload to SQL Server CPU, but I believe it worths to this CPU load.
The last option is "Continue on error", why it is there I don't know actually. I never want to keep a file which is probably causing error messages.

SQL Backup Compression

An other important option which is introduced with Microsoft SQL Server 2008 is the on the fly sql backup compression.
We know there are third-party tools supporting backup compression for various database products.
Now, Microsoft also supports SQL Server backup compression.
Compression will cause CPU workload on the server, on the other hand will generate smaller backup files in sizes. Small sizes for sql backups means less storage requirement and low bandwidth during network transfers of the data files.

Click OK button to start SQL Server backup task.

Here is a successfull SQL Server database backup process.

Microsoft SQL Server Management Studio
------------------------------
The backup of database 'AdventureWorks2008R2' completed successfully.