Ashok Kumar SQL Development and DBA Adminstrations Techi Blog : Backup Types In SQL Server 2005

SQL Server Backup Types & Methods:

Overview
One of your last lines of defense for just about any system is to have a backup in place in case there is a need to recover some or all of your data. This is also true for SQL Server.

In this tutorial we will discuss

selecting the correct recovery models
what backup options are available
how to create backups using T-SQL commands and SQL Server Management Studio

If you are new to SQL Server you should review each of these topics, so you are aware of the available options and what steps you will need to take in order to recover your data if ever there is the need.

You can either use the outline on the left or click on the arrows to the right or below to scroll through each of these

SQL Server Recovery Models

(SET RECOVERY)

Overview
One of the first things that needs to be set in order to create the correct backups is to set the proper recovery model for each database. The recovery model basically tells SQL Server what data to keep in the transaction log file and for how long. Based on the recovery model that is selected, this will also determine what types of backups you can perform and also what types of database restores can be performed.

Explanation
The three types of recovery models that you can choose from are:

Each database can have only one recovery model, but each of your databases can use a different recovery model, so depending on the processing and the backup needs you can select the appropriate recovery model per database. The only exception to this is the TempDB database which has to use the "Simple" recovery model.

Also, the database recovery model can be changed at any time, but this will impact your backup chain, so it is a good practice to issue a full backup after you change your recovery model.

The recovery model can be changed by either using T-SQL or SQL Server Management Studio. Following are examples on how to do this.

Using T-SQL to change to the "Full" recovery for the AdventureWorks database.

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO

Using the SSMS to change the recovery model for the AdventureWorks database.

SQL Server Full Recovery Model

(SET RECOVERY FULL)

Overview
The "Full" recovery model tells SQL Server to keep all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated. The way this works is that all transactions that are issued against SQL Server first get entered into the transaction log and then the data is written to the appropriate data file. This allows SQL Server to rollback each step of the process in case there was an error or the transaction was cancelled for some reason. So when the database is set to the "Full" recovery model since all transactions have been saved you have the ability to do point in time recovery which means you can recover to a point right before a transaction occurred like an accidental deletion of all data from a table.

Explanation
The full recovery model is the most complete recovery model and allows you to recover all of your data to any point in time as long as all backup files are useable. With this model all operations are fully logged which means that you can recover your database to any point. In addition, if the database is set to the full recovery model you need to also issue transaction log backups otherwise your database transaction log will continue to grow forever.

Here are some reasons why you may choose this recovery model:

Data is critical and data can not be lost.
You always need the ability to do a point-in-time recovery.
You are using database mirroring

Type of backups you can run when the data is in the "Full" recovery model:

Complete backups
Differential backups
File and/or Filegroup backups
Partial backups
Copy-Only backups
Transaction log backups

How to set the full recovery model using T-SQL.

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

Example: change AdventureWorks database to "Full" recovery model

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO

How to set using SQL Server Management Studio

Right click on database name and select Properties
Go to the Options page
Under Recovery model select "Full"
Click "OK" to save

SQL Server Bulk-Logged Recovery Model

(SET RECOVERY BULK_LOGGED)

Overview
The "Bulk-logged" recovery model sort of does what it implies. With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... that are not fully logged in the transaction log and therefore do not take as much space in the transaction log.

Explanation
The advantage of using the "Bulk-logged" recovery model is that your transaction logs will not get that large if you are doing bulk operations and it still allows you to do point in time recovery as long as your last transaction log backup does not include a bulk operation as mentioned above. If no bulk operations are run this recovery model works the same as the Full recovery model. One thing to note is that if you use this recovery model you also need to issue transaction log backups otherwise your database transaction log will continue to grow.

Here are some reasons why you may choose this recovery model:

Data is critical, but you do not want to log large bulk operations
Bulk operations are done at different times versus normal processing.
You still want to be able to recover to a point in time

Type of backups you can run when the data is in the "Simple" recovery model:

Complete backups
Differential backups
File and/or Filegroup backups
Partial backups
Copy-Only backups
Transaction log backups

How to set the bulk-logged recovery model using T-SQL.

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

Example: change AdventureWorks database to "Bulk-logged" recovery model

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED
GO

How to set using SQL Server Management Studio

Right click on database name and select Properties
Go to the Options page
Under Recovery model select "Bulk-logged"
Click "OK" to save

Types of SQL Server Backups

Overview
SQL Server offers many options for creating backups. In a previous topic, Recovery Models, we discussed what types of backups can be performed based on the recovery model of the database. In this section we will talk about each of these backup options and how to perform these backups using SSMS and T-SQL.

Explanation
The different types of backups that you can create are as follows:

SQL Server Full Backups

Overview
The most common types of SQL Server backups are complete or full backups, also known as database backups. These backups create a complete backup of your database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration, since all of the contents are contained in one backup.

Explanation
A full backup can be completed either using T-SQL or by using SSMS. The following examples show you how to create a full backup.

Create a full backup of the AdventureWorks database to one disk file

T-SQL

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK'

SQL Server Management Studio

Right click on the database name
Select Tasks > Backup
Select "Full" as the backup type
Select "Disk" as the destination
Click on "Add..." to add a backup file and type "C:\AdventureWorks.BAK" an click "OK"
Click "OK" again to create the backup

http://www.mssqltips.com/tutorialimages/7_Full2.jpg

SQL Server Transaction Log Backups

Overview
If your database is set to the "Full" or "Bulk-logged" recovery model then you will be able to issue "Transaction Log" backups. By having transaction log backups along with full backups you have the ability to do a point in time restore, so if someone accidently deletes all data in a database you can recover the database to the point in time right before the delete occurred. The only caveat to this is if your database is set to the "Bulk-logged" recovery model and a bulk operation was issued, you will need to restore the entire transaction log.

Explanation
A transaction log backup allows you to backup the active part of the transaction log. So after you issue a "Full" or "Differential" backup the transaction log backup will have any transactions that were created after those other backups completed. After the transaction log backup is issued, the space within the transaction log can be reused for other processes. If a transaction log backup is not taken, the transaction log will continue to grow.

A transaction log backup can be completed either using T-SQL or by using SSMS. The following examples show you how to create a transaction log backup.

Create a transaction log backup of the AdventureWorks database to one disk file

T-SQL

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN'

SQL Server Management Studio

Right click on the database name
Select Tasks > Backup
Select "Transaction Log" as the backup type
Select "Disk" as the destination
Click on "Add..." to add a backup file and type "C:\AdventureWorks.TRN" and click "OK"
Click "OK" again to create the backup

http://www.mssqltips.com/tutorialimages/8_Tran3.jpg

SQL Server Differential Backups

Overview
Another option to assist with your recovery is to create "Differential" backups. A "Differential" backup is a backup of any extent that has changed since the last "Full" backup was created.

Explanation
The way differential backups work is that they will backup all extents that have changed since the last full backup. An extent is made up of eight 8KB pages, so an extent is 64KB of data. Each time any data has been changed a flag is turned on to let SQL Server know that if a "Differential" backup is created it should include the data from this extent. When a "Full" backup is taken these flags are turned off.

So if you do a full backup and then do a differential backup, the differential backup will contain only the extents that have changed. If you wait some time and do another differential backup, this new differential backup will contain all extents that have changed since the last full backup. Each time you create a new differential backup it will contain every extent changed since the last full backup. When you go to restore your database, to get to the most current time you only need to restore the full backup and the most recent differential backup. All of the other differential backups can be ignored.

If your database is in the Simple recovery model, you can still use full and differential backups. This does not allow you to do point in time recovery, but it will allow you to restore your data to a more current point in time then if you only had a full backup.

If your database is in the Full or Bulk-Logged recovery model you can also use differential backups to eliminate the number of transaction logs that will need to be restored. Since the differential will backup all extents since the last full backup, at restore time you can restore your full backup, your most recent differential backup and then any transaction log backups that were created after the most recent differential backup. This cuts down on the number of files that need to be restored.

Create a differential backup of the AdventureWorks database to one disk file

T-SQL

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.DIF' WITH DIFFERENTIAL

SQL Server Management Studio

Right click on the database name
Select Tasks > Backup
Select "Differential" as the backup type
Select "Disk" as the destination
Click on "Add..." to add a backup file and type "C:\AdventureWorks.DIF" and click "OK"
Click "OK" again to create the backup

http://www.mssqltips.com/tutorialimages/9_Diff1.jpg

SQL Server File Backups

Overview
Another option for backing up your databases is to use "File" backups. This allows you to backup each file independently instead of having to backup the entire database. This is only relevant when you have created multiple data files for your database. One reason for this type of backup is if you have a very large files and need to back them up individually. For the most part you probably only have one data file, so this is option is not relevant.

Explanation
As mentioned above you can back up each data file individually. If you have a very large database and have large data files this option may be relevant.

A file backup can be completed either using T-SQL or by using SSMS. The following examples show you how to create a transaction log backup.

Create a file backup of the TestBackup database

For this example I created a new database called TestBackup that has two data files and one log file. The two data files are called 'TestBackup' and 'TestBackup2'. The code below shows how to backup each file separately.

T-SQL

BACKUP DATABASE TestBackup FILE = 'TestBackup'

TO DISK = 'C:\TestBackup_TestBackup.FIL'

BACKUP DATABASE TestBackup FILE = 'TestBackup2'

TO DISK = 'C:\TestBackup_TestBackup2.FIL'

SQL Server Management Studio

Right click on the database name
Select Tasks > Backup
Select either "Full" or "Differential" as the backup type
Select "Files and filegroups"
Select the appropriate file and click "OK"

http://www.mssqltips.com/tutorialimages/18_fil2.jpg

Select "Disk" as the destination
Click on "Add..." to add a backup file and type "C:\TestBackup_TestBackup.FIL" and click "OK"
Click "OK" again to create the backup and repeat for other files

http://www.mssqltips.com/tutorialimages/18_fil3.jpg

SQL Server Filegroup Backups

Overview
In addition to doing "File" backups you can also do "Filegroup" backups which allows you to backup all files that are in a particular filegroup. By default each database has a PRIMARY filegroup which is tied to the one data file that is created. You have an option of creating additional filegroups and then placing new data files in any of the filegroups. In most cases you will probably only have the PRIMARY filegroup, so this is topic is not relevant.

Explanation
As mentioned above you can back up each filegroup individually. The one advantage of using filegroup backups over file backups is that you can create a Read-Only filegroup which means the data will not change. So instead of backing up the entire database all of the time you can just backup the Read-Write filegroups.

A filegroup backup can be completed either using T-SQL or by using SSMS.

Create a filegroup backup of the TestBackup database

For this example I created a new database called TestBackup that has three data files and one log file. Two data files are the PRIMARY filegroup and one file is in the ReadOnly filegroup. The code below shows how to do a filegroup backup.

T-SQL

BACKUP DATABASE TestBackup FILEGROUP = 'ReadOnly'

TO DISK = 'C:\TestBackup_ReadOnly.FLG'

SQL Server Management Studio

Right click on the database name
Select Tasks > Backup
Select either "Full" or "Differential" as the backup type
Select "Files and filegroups"
Select the appropriate filegroup and click "OK"

http://www.mssqltips.com/tutorialimages/17_Fil4.jpg

Select "Disk" as the destination
Click on "Add..." to add a backup file and type "C:\TestBackup_ReadOnly.FLG" and click "OK"
Click "OK" again to create the backup and repeat for other filegroups

http://www.mssqltips.com/tutorialimages/17_Fil5.jpg

SQL Server Partial Backups

Overview
A new option is "Partial" backups which was introduced with SQL Server 2005. This allows you to backup the PRIMARY filegroup, all Read-Write filegroups and any optionally specified files. This is a good option if you have Read-Only filegroups in the database and do not want to backup the entire database all of the time.

Explanation
A Partial backup can be issued for either a Full or Differential backup. This can not be used for Transaction Log backups. If a filegroup is changed from Read-Only to Read-Write it will be included in the next Partial backup, but if you change a filegroup from Read-Write to Read-Only you should create a filegroup backup, since this filegroup will not be included in the next Partial backup.

A partial backup can be completed only by using T-SQL. The following examples show you how to create a partial backup.

Create a partial backup of the TestBackup database

T-SQL

Create a full partial backup

BACKUP DATABASE TestBackup READ_WRITE_FILEGROUPS

TO DISK = 'C:\TestBackup_Partial.BAK'

Create a differential partial backup

BACKUP DATABASE TestBackup READ_WRITE_FILEGROUPS TO DISK = 'C:\TestBackup_Partial.DIF' WITH DIFFERENTIAL GO
SQL Server Backup Commands

Overview
There are primarily two commands that are used to create SQL Server backups. Which are:

These commands also have various options that you can use to create full, differential, file, transaction log backups, etc... as well as other options to specify how the backup command should function and any other data to store with the backups.

SQL Server BACKUP DATABASE command

(BACKUP DATABASE)

Overview
There are only two commands for backup, the primary is BACKUP DATABASE. This allows you to do a complete backup of your database as well as differential, file, etc. backups depending on the options that you use.

Explanation
The BACKUP DATABASE command gives you many options for creating backups. Following are different examples.

Create a full backup to disk
The command is BACKUP DATABASE databaseName. The "TO DISK" option specifies that the backup should be written to disk and the location and filename to create the backup is specified.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

Create a differential backup
This command adds the "WITH DIFFERENTIAL" option.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

WITH DIFFERENTIAL

Create a file level backup
This command uses the "WITH FILE" option to specify a file backup. You need to specify the logical filename within the database which can be obtained by using the command sp_helpdb 'databaseName', specifying the name of your database.

BACKUP DATABASE TestBackup FILE = 'TestBackup'

TO DISK = 'C:\TestBackup_TestBackup.FIL'

Create a filegroup backup
This command uses the "WITH FILEGROUP" option to specify a filegroup backup. You need to specify the filegroup name from the database which can be obtained by using the command sp_helpdb 'databaseName', specifying the name of your database.

BACKUP DATABASE TestBackup FILEGROUP = 'ReadOnly'

TO DISK = 'C:\TestBackup_ReadOnly.FLG'

Create a full backup to multiple disk files
This command uses the "DISK" option multiple times to write the backup to three equally sized smaller files instead of one large file.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks_1.BAK',

DISK = 'D:\AdventureWorks_2.BAK',

DISK = 'E:\AdventureWorks_3.BAK'

Create a full backup with a password
This command creates a backup with a password that will need to be supplied when restoring the database.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

WITH PASSWORD = 'Q!W@E#R$'

Create a full backup with progress stats
This command creates a full backup and also displays the progress of the backup. The default is to show progress after every 10%.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

WITH STATS

Here is another option showing stats after every 1%.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

WITH STATS = 1

Create a backup and give it a description
This command uses the description option to give the backup a name. This can later be used with some of the restore commands to see what is contained with the backup. The maximum size is 255 characters.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

WITH DESCRIPTION = 'Full backup for AdventureWorks'

Create a mirrored backup
This option allows you to create multiple copies of the backups, preferably to different locations.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

MIRROR TO DISK = 'D:\AdventureWorks_mirror.BAK'

WITH FORMAT

Specifying multiple options
This next example shows how you can use multiple options at the same time.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' MIRROR TO DISK = 'D:\AdventureWorks_mirror.BAK' WITH FORMAT, STATS, PASSWORD = 'Q!W@E#R$' GO
SQL Server BACKUP LOG command (BACKUP LOG)

Overview
There are only two commands for backup, the primary is BACKUP DATABASE which backs up the entire database and BACKUP LOG which backs up the transaction log. The following will show different options for doing transaction log backups.

Explanation
The BACKUP LOG command gives you many options for creating transaction log backups. Following are different examples.

Create a simple transaction log backup to disk
The command is BACKUP LOG databaseName. The "TO DISK" option specifies that the backup should be written to disk and the location and filename to create the backup is specified. The file extension is "TRN". This helps me know it is a transaction log backup, but it could be any extension you like. Also, the database has to be in the FULL or Bulk-Logged recovery model and at least one Full backup has to have occurred.

BACKUP LOG AdventureWorks

TO DISK = 'C:\AdventureWorks.TRN'

Create a log backup with a password
This command creates a log backup with a password that will need to be supplied when restoring the database.

BACKUP LOG AdventureWorks

TO DISK = 'C:\AdventureWorks.TRN'

WITH PASSWORD = 'Q!W@E#R$'

Create a log backup with progress stats
This command creates a log backup and also displays the progress of the backup. The default is to show progress after every 10%.

BACKUP LOG AdventureWorks

TO DISK = 'C:\AdventureWorks.TRN'

WITH STATS

Here is another option showing stats after every 1%.

BACKUP LOG AdventureWorks

TO DISK = 'C:\AdventureWorks.TRN'

WITH STATS = 1

BACKUP LOG AdventureWorks

TO DISK = 'C:\AdventureWorks.TRN'

WITH DESCRIPTION = 'Log backup for AdventureWorks'

Create a mirrored backup
This option allows you to create multiple copies of the backups, preferably to different locations.

BACKUP LOG AdventureWorks

TO DISK = 'C:\AdventureWorks.TRN'

MIRROR TO DISK = 'D:\AdventureWorks_mirror.TRN'

WITH FORMAT

Specifying multiple options
This example shows how you can use multiple options at the same time.

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN' MIRROR TO DISK = 'D:\AdventureWorks_mirror.TRN' WITH FORMAT, STATS, PASSWORD = 'Q!W@E#R$' GO
How to create a SQL Server backup

Overview
Creating backups for SQL Server is very easy. There are a few things you need to consider:

How will you create the backups:

T-SQL commands
Using SQL Server Management Studio
Creating maintenance plans and
Using third party backup tools

What options will you use

Backup to disk or to tape
Types of backups; full, differential, log, etc...

The next two topics cover the basics on how to create backups using either T-SQL or SQL Server Management Studio.

Creating a backup using SQL Server Management Studio

Overview
Creating backups using SQL Server Management Studio is pretty simple as well. Based on how simple the T-SQL commands are, there is a lot of clicking that needs to occur in SSMS to create a backup.

Explanation
The following screen shots show you how to create a full backup and a transaction log backup.