Page 138 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes Introduction

In the last unit you have studied about database recovery models. In this unit, you will learn
about back up types and options. This unit will help you understand the various types of backup
strategies available in SQL Server enterprise.
It is highly recommended that all SQL Server databases be backed up periodically. This provides
the best chance of successfully recovering a production environment in the quickest amount of
time in case there is a disaster situation.

This unit discusses the various kinds of backups that are available in SQL Server 2000 and how
this functionality may be used in a disaster recovery plan. The unit also discusses some general
recommendations on how to improve backup and restore throughput.

10.1 Backup Types and Options

A copy of data that can be used to restore and recover the data is called a backup. Backups let you
restore data after a failure. With good backups, you can recover from many failures, such as:

Media failure.
User errors, for example, dropping a table by mistake.
Hardware failures, for example, a damaged disk drive or permanent loss of a server.

Natural disasters.
Additionally, backups of a database are useful for routine administrative purposes, such as
copying a database from one server to another, setting up database mirroring, and archiving.

10.1.1 Types of Backups

SQL Server provides several different kinds of backups. A combination of these backups may be
used to formulate a robust disaster recovery strategy.

Scope of Backup

The scope of a backup of data (a data backup) can be a whole database, a partial database, or a set
of files or filegroups. For each of these, SQL Server supports full and differential backups:
1. Full backup: A full backup contains all the data in a specific database or set of filegroups or
files, and also enough log to allow for recovering that data.
2. Differential backup: A differential backup is based on the latest full backup of the data.
This is known as the base of the differential, or the differential base. A differential base is
a full backup of read/write data. A differential backup contains only the data that has
changed since the differential base. Typically, differential backups that are taken fairly
soon after the base backup are smaller and faster to create than the base of a full backup.
Therefore, using differential backups can speed up the process of making frequent backups
to decrease the risk of data loss. Usually, a differential base is used by several successive
differential backups. At restore time, the full backup is restored first, followed by the most
recent differential backup.
Over time, as a database is updated, the amount of data that is included in differential backups
increases. This makes the backup slower to create and to restore. Eventually, another full backup
will have to be created to provide a new differential base for another series of differential
backups.

132 LOVELY PROFESSIONAL UNIVERSITY