Page 140 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes SQL Server supports the following types of file backups.

Backup type Description
File backup A full backup of all the data in one or more files, or filegroups.
Important
Under the simple recovery model, file backups are basically restricted to read-only
secondary filegroups. You can create a file backup of a read/write filegroup, but
before you can restore the read/write file backup, you must set the filegroup to read-
only and take a differential read-only file backup.
Differential A backup of one or more files that contain data extents that were changed since the
file backups most recent full backup of each file.
Note
Under the simple recovery model, this assumes that the data has been changed to
read-only since the full backup.

Under the full recovery model or bulk-logged recovery model, regular transaction log backups
(or log backups) are required. Each log backup covers the part of the transaction log that was
active when the backup was created, and it includes all log records that were not backed up in a
previous log backup. An uninterrupted sequence of log backups contains the complete log chain
of the database, which is said to be unbroken. Under the full recovery model, and sometimes
under the bulk-logged recovery model, an unbroken log chain lets you to restore the database
to any point in time.

Before you can create the first log backup, you must create a full backup, such as a database
backup. Thereafter, backing up the transaction log regularly is necessary, not only to minimize
work-loss exposure but also to enable truncation of the transaction log.

Notes To limit the number of log backups that you need to restore, it is essential to
routinely back up your data. For example, you might schedule a weekly full database
backup and daily differential database backups.

Copy-Only Backups

Usually, taking a backup changes the database and affects how later backups are restored.
However, occasionally, it is useful to take a backup for a special purpose without affecting the
overall backup and restore procedures for the database. For this purpose, copy-only backups
were introduced in SQL Server 2005. These backups are independent of the regular sequence of
SQL Server backups.
Summary of back up types in SQL server

Backup Type Description
Complete Backs up the entire database.
Differential Backs up only modified extents since the previous complete backup.
Transaction Log Backs up the active portion and truncates the inactive portion of the transaction log.
File / Filegroup Backs up individual files and filegroups within a database.
File differential Combines differential backups and file or filegroup backups.

134 LOVELY PROFESSIONAL UNIVERSITY