A full backup backs up the entire database, including part of the transaction log (so that the full backup can be recovered). Full backups represent the database at the time the backup completed.
The backup engine accomplishes this task by extracting every extent in the database that is allocated to an object. You can then use a full backup by itself to re-create the entire database.
A differential backup captures all the extents that have changed since the last full backup. And the main purpose of a differential backup is to reduce the number of transaction log backups that need to be restored. You use a differential backup along with a full backup. If a full backup does not exist, you cannot create a differential backup.
To determine the extents that need to be backed up by a differential backup, SQL
Server maintains an extent map. An extent map is just another data page within the database, with each bit on the page representing an extent. When SQL Server changes an extent, it changes the corresponding bit for that extent from 0 to 1. When you perform a full backup, SQL Server resets all bits to 0. In this way, SQL Server has to interrogate only this page to determine which extents it needs to back up. Because databases can be an unlimited size and data pages are only 8 KB in size, SQL Server creates one of these mapping pages for approximately every 8,192 extents that it allocates to objects in the database. So a single page can cover thousands of data pages.
Transaction log backups are also allowed only after a full backup has been performed.
A log backup backs up the active log. It starts at the Log Sequence Number (LSN) at which the previous log backup completed. SQL Server then backs up all subsequent transactions until the backup encounters an open transaction. After SQL Server encounters an open transaction, the log backup completes. Any LSNs that are backed up are then allowed to be removed from the transaction log, which enables the system to reuse log space.
Backup Media
SQL Server databases may be backed up to either a disk or tape media.
Disk Backups
A database may be backed up to disk file or a disk backup device.
Create a disk backup device
Tape Backups
A database may be backed up to a local tape drive. SQL Server formats the tape backups using Microsoft Tape Format (MTF). This means that a tape may hold other backups formatted using MTF in conjunction with SQL Server backups.
Tape backups provide certain features that are not available when using disk backups.
Continuation media
If the tape to which the backup is being written fills up, SQL Server Enterprise Manager pops a dialog box and prompts for the next tape (if using the Transact-SQL command, a message is logged to the SQL Server error log to mount the next tape and a retry attempt is made roughly every five minutes to see if a new tape was mounted). This is in contrast to disk backups, where inadequate disk space terminates the backup operation.
Media Set
A given media set uses either tape drives or disk drives, but not both.
Example 1:
If a computer has three tape drives à
\\.\TAPE0,
\\.\TAPE1, and \\.\TAPE2, you can create a media
set with a tape per tape drive, as shown--a media set has three tapes.
If a computer has four tape drives à\\.\TAPE0, \\.\TAPE1, \\.\TAPE2, and \\.TAPE3, you can create a media set with five tapes per tape drive.
The media set has 20 tapes.
Backup Sets
A successful backup operation adds a single backup set to the media set. If the media set consists of only one media family, e.g. a media set with only one tape drive, the family contains the entire backup set. If the media set consists of multiple media families, e.g. a media set with three files, the backup set is distributed among them.
A media set can contain one or more backup sets.
Example:
BACKUP DATABASE AdventureWorks
TO TAPE = '\\.\tape0', TAPE = '\\.\tape1', TAPE = '\\.\tape2'
WITH
FORMAT,
MEDIANAME = 'AdventureWorksMediaSet1'
If successful, this backup operation will create a new media set: MyAdvWorks_MediaSet_1 and one backup set spread across three tapes.
The command creates a media set with three tapes, which come from three families (tape drive).
Typically, after a media set is created, subsequent backup operations, one after another, append their backup sets to the media set. Backup sets are sequentially numbered by their position in the media set, allowing you to specify which backup set to restore.
----------------------------------------------------------------------------
Media header Media Header Media Header
Backup Content Backup Content Backup Content
______________________________________________________
A second backup operation (differential backup) appends information to the same media set:
BACKUP DATABASE AdventureWorks
TO TAPE = '\\.\tape0', TAPE = '\\.\tape1', TAPE = '\\.\tape2'
WITH
NOINIT,
MEDIANAME = 'AdventureWorksMediaSet1',
DIFFERENTIAL
If the second backup operation succeeds, it writes a second backup set to the media set, with the following distribution of backup content:
Tape 0 Tape 1 Tape 2
----------------------------------------------------------------------------
Media header Media Header Media Header
Backup Content Backup Content Backup Content
backup content Backup Content Backup Content
______________________________________________________
