Backup Targets
Besides the backup type, SQL Server allows four different
backup targets: Database, Filegroup, File and Partial. These targets only apply
to the Full and Differential Backup types; they do not apply to the log backup.
Database Backup
A database backup makes a copy of the entire database. If
you are using a Full database backup it includes all used database pages in the
database; if you are using a Differential Database backup, it includes all
changed database pages of the database that have changed since the last full
backup. Some examples of how to perform a Full and Differential Database backup
are:
-- Full Database Backup
BACKUP DATABASE AdventureWorks
TO DISK = N’C:\Backup\AdventureWorks.bak’
-- Differential Database Backup
BACKUP DATABASE AdventureWorks
TO DISK = N’C:\Backup\AdventureWorks.bak’
WITH DIFFERENTIAL
Differential Backup
Differential backups are designed to reduce the amount of time
it takes to perform full backups. Instead of copying all used pages, the
differential backup copies only pages that have changed since the last full
backup. Similar to the Full Backup, differential backups also copy a part of the
transaction log to maintain the transactional integrity when the backup is
restored. The following code is an example of how to create a Differential
Backup.
BACKUP DATABASE AdventureWorks
TO DISK = N’C:\Backup\AdventureWorks.bak’
WITH DIFFERENTIAL
Log Backup
The only type of backup that does not actually copy database
pages is the Log backup. The Log backup copies the transaction log of the
database. After it copies the log, the backup truncates the part of the log
that is not required by active transactions or transactional replication. The syntax
to back up the Log is:
BACKUP LOG AdventureWorks
TO DISK =
N’C:\Backup\AdventureWorks.bak’
File Backup
File backups do not copy the database; instead the file backup
aim is to back up only one or more files that are part the database.Databases
in SQL server 2005 have three different types of files: Primary, Secondary, or
Log Files. Primary and Secondary files hold database pages; in contrast, Log
files hold the transaction log. File backups can make copies of pages from
Primary and Secondary Files.This type of backup can be used to speed up the
restora-tion process, when the device that stores one of the files
fails but the rest of the devices are working correctly. In this
case, there is no need to restore the full database; only the affected
file. The following SQL code performs
Full and Differential File backups:
-- Full File Backup
BACKUP DATABASE AdventureWorks FILE=’AdventureWorks_Data’
TO DISK =
N’C:\Backup\AdventureWorks.bak’
-- Differential File Backup
BACKUP DATABASE AdventureWorks FILE=’AdventureWorks_Data’
TO DISK =
N’C:\Backup\AdventureWorks.bak’
WITH DIFFERENTIAL
One important element to remember when using File Backups is
that they should either be read only (the file-group they belong to) or
combined with Log Backups to restore transactional integrity of the database.
File backups do not automatically back up the transaction log.
