SQL Server 2005 Backup Types
Full, Transaction Log, Differential, Partial, Differential Partial, File and Filegroup, and Copy Only Database Backups
Full Database Backup
A full database backup captures the entire database in one fell
swoop. It can be used to restore a database to the time when the backup
completed. A full database backup is often used during restore
operations as a first step in recovering a database. Other backups are
applied after the full backup is restored to bring the database closer
in time to the actual moment of failure.
Data Loss:
A full database backup will not on its own provide full recovery up
to the point of failure. A full database backup for SQL Server 2005
restores the database up to the time when the backup was completed. If
you have more stringent data loss requirements – and most organizations
will – you need to supplement your full database backups with other
types of backups. Your best chance at preventing data loss is to use
the full recovery model with transaction logs.
Size:
A full database backup will be the largest type of backup that there is.
Resources Required:
A full database backup requires a lot of system resources and can negatively impact system performance.
(back to top)
Transaction Log Backup
Only available under the full recovery model and the bulk logged
recovery model. Transaction log backups allow for point in time
recovery since each transaction is captured within the transaction log.
Data Loss:
The use of transaction log backups provides point in time recovery.
As far as backups are concerned, the use of full backups combined with
transaction logs can provide point in time recovery capabilities and
minimize data loss.
Size:
Compared to other types of database backups, transaction log backups aren't very large if they are scheduled regularly.
Resources Required:
Generally speaking, transaction log backups take up very little in the way of system resources.
(back to top)
Differential Database Backup
A differential database backup captures changes (and only the
changes) made to the database since the last full database backup. This
full database backup which differential backups reference is also
called the differential base, since the differential backups are
recording changes since the last full database backup was performed.
The differential database backup records the changes to the database up
to the time when the differential backup was completed.
Data Loss:
A differential backup provides additional protection from data loss
than a full database backup, but does not on its own guarantee that some
data won’t be lost. Your best chance at preventing data loss is to use
the full recovery model with transaction logs.
Size:
The differential database backup will likely be a fraction of the
size of the full database backup since only changes since the last full
database backup are saved. However, if you have multiple differential
database backups between each full database backup, each differential
database backup will be larger than the one before it, since all
differential database backups record ALL changes since the last full
database backup.
Resources Required:
The overhead required to perform a differential backup is
significantly less than what is required for a full database backup
since only the changes since the last full database backup are being
backed up.
(back to top)
Partial Database Backup
Partial backups were introduced for SQL Server 2005. Partial backups
will backup the primary filegroup, any read/write file groups, and any
read-only files that are specified during the backup. The partial
backup is designed to not backup any data that is placed in a read-only
file group. So, you can use a partial backup to backup your entire
database – except for the read only data. Partial backups are meant to
be used in a simple recovery model situation, although they can be used
in a full recovery model situation as well.
Data Loss:
The partial backup provides bare minimum protection from data loss.
The partial backup serves as a base for differential partial backups –
which can be applied to the partial backup to bring the database closer
to a point in time near the actual database failure. A full recovery
model with transaction logs provides the best chance to restore the
database up to the point of failure.
Size:
A key advantage of using a partial backup as opposed to a full
database backup is the reduced file size. The read only filegroups only
have to be saved once, therefore the partial backups will be smaller
than what would be required for a full database backup.
Resources Required:
The strain on the database will likely be much less for a partial
database backup than it is for a full database backup since specified
read only filegroups won’t have to be backed up.
(back to top)
Differential Partial Database Backup
Differential partial backups use the most recent partial backup as
their differential base. They work very similar to a differential
backup, except (with some exceptions) they capture only changes made to
read/write filegroups .
If you add, drop, or change the status of any of your filegroups,
you’re best off taking a partial backup prior to resuming differential
partial backups. Filegroup changes can have unintended consequences
with differential partial backups.
Data Loss:
Differential Partial provide a reduced chance of data loss from a
partial backup alone, since the differential partial backups are
performed between partial backups.
Size:
A differential partial backup should be relatively small given that
it won’t backup specified read only filegroups and uses the most recent
partial backup as its base.
Resources Required:
The resources required for a differential partial backup will be
relatively small compared to full database backups and partial backups.
(back to top)
File and Filegroup Backups
This primarily applies to very large databases (VLDB) with multiple
filegroups that have strict availability requirements. In these
instances, sometimes it takes too long to perform a full backup, or the
size of a full backup is extremely large.
If you add, drop, or change the status of any of your filegroups,
you’re best off taking a partial backup prior to resuming differential
partial backups. Filegroup changes can have unintended consequences
with differential partial backups.
Data Loss:
Data loss is minimized with file and filegroup backups through the
proper use of transaction log backups. File and Filegroup backups can
only be performed under a full recovery model or a bulk logged recovery
model.
Size:
The size of individual file or filegroup backups will obviously be
smaller than the size required for a full database backup – which is one
of the key advantages of using file and filegroup backups in VLDB
situations.
Resources Required:
While there is overhead involved in backing up any portion of a VLDB,
the resources will likely be much less than the resources required for
performing a full database backup on a very large database.
(back to top)
Copy Only Database Backup
Copy Only backups in SQL Server 2005 must be performed using T-SQL
and are not logged in the backup and restore sequence for the database.
In other words, you can’t apply differential backups to this database
backup, it is an independent copy separate from the original database.
The transaction log is unaffected (will not be truncated) during this
type of backup. You might consider making a copy only backup for a
special purpose – like database development or testing.
Data Loss:
Not applicable. A copy only database backup is used to make a copy
of the database and isn’t part of a backup and recovery plan.
Size:
Not Applicable. Not part of the overall backup and recovery plan.
Resources Required:
Resources required for a Copy Only backup would be similar to a full database backup.
