Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 8 March 2013

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'
GO
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'
GO
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
GO
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'
GO
BACKUP DATABASE TestBackup FILE = 'TestBackup2'
TO DISK = 'C:\TestBackup_TestBackup2.FIL'
GO
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'
GO
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
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 partial backup.
T-SQL
Create a full partial backup
BACKUP DATABASE TestBackup READ_WRITE_FILEGROUPS
TO DISK = 'C:\TestBackup_Partial.BAK'
GO
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'
GO
Create a differential backup
This command adds the "WITH DIFFERENTIAL" option.
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks.BAK'
WITH DIFFERENTIAL
GO
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'
GO
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'
GO
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'
GO
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$'
GO
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
GO
Here is another option showing stats after every 1%.
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks.BAK'
WITH STATS = 1
GO
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'
GO
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
GO
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'
GO
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$'
GO
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
GO
Here is another option showing stats after every 1%.
BACKUP LOG AdventureWorks
TO DISK = 'C:\AdventureWorks.TRN'
WITH STATS = 1
GO
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 LOG AdventureWorks
TO DISK = 'C:\AdventureWorks.TRN'
WITH DESCRIPTION = 'Log backup for AdventureWorks'
GO
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
GO
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.
  • Expand the "Databases" tree
  • Right click on the database name you want to backup
  • Select "Tasks" then "Back Up..." as shown below
http://www.mssqltips.com/tutorialimages/25_SSM1.jpg
  • Specify the "Backup type"; Full, Differential or Transaction Log
http://www.mssqltips.com/tutorialimages/25_SSM2.jpg
  • Click on "Add..." to add the location and the name of the backup file
  • Click "OK" to close this screen
http://www.mssqltips.com/tutorialimages/25_SSM3.jpg
  • And click "OK" again to create the backup
http://www.mssqltips.com/tutorialimages/25_SSM4.jpg