Backing Up and Restoring SQL Server Databases and Transaction Logs

General Concepts

The backing up and restoring of databases and transaction logs is a way that
SQL Server provides to protect from data loss. The backup operation creates
a copy of a database. This copy can be used to restore the database if media
failure occurs or if the database is somehow damaged (for example, from user errors).

SQL Server 2000 supports the following kinds of backup:

  • Full database backup (it is a full copy of the database).
  • Transaction log backup (it contains a copy of the transaction log only).
  • Differential backup (it contains a copy of only the database pages
    modified since the last full database backup).

SQL Server 2000 supports three recovery models which determine how your data
is backed up and what your exposure to data loss is. There are:

  • Simple recovery model
  • Full recovery model
  • Bulk-Logged recovery model

Simple Recovery model

The Simple Recovery model allows the database to be recovered to the point
of the last backup (to the most recent backup). With this recovery model,
you cannot restore the database to the point of failure or to a specific
point in time. Therefore, changes since the last backup will be lost.

The backup strategy for this recovery model consists of the full database
backups only or full database backups and some differential backups. To
recover a database, you should restore the most recent full database backup
and then restore the most recent differential backup if it exists. The
Simple Recovery model is similar to setting the trunc. log on chkpt database
option in SQL Server 7.0 or earlier. This recovery model takes less time to
perform the backup and restore in comparison with the Full and Bulk-Logged
models and requires less disk space, but does not provide the opportunity to
restore the database to the point of failure or to a specific point in time.

For example, if you make a full database backup of the pubs database on a
Sunday at 1 AM, and make a differential backup of the pubs database on Monday
at 1 AM and again on Tuesday at 1 AM, then suppose that the pubs database is
damaged on Tuesday at 3 AM, you should restore the full database backup
from Sunday 1 AM, and then restore the differential backup from Tuesday 1 AM.
All changes since Tuesday 1 AM will be lost.


— Create a full database backup of the pubs database on Sunday at 1AM
BACKUP DATABASE pubs TO pubs_back WITH INIT
GO
— Time elapses.
— Create a differential backup of the pubs database on Monday at 1AM
BACKUP DATABASE pubs TO pubs_back WITH DIFFERENTIAL
GO
— Time elapses.
— Create a differential backup of the pubs database on Tuesday at 1AM
BACKUP DATABASE pubs TO pubs_back WITH DIFFERENTIAL
GO

— Time elapses.
— The pubs database was damaged on Tuesday at 3 AM
— Restore the full database from Sunday 1 AM without recovering
RESTORE DATABASE pubs FROM pubs_back WITH NORECOVERY
GO
— Restore the differential backup from Tuesday 1 AM with recovering
RESTORE DATABASE pubs FROM pubs_back WITH FILE = 3, RECOVERY
GO

Full Recovery model

The Full Recovery model allows the database to be recovered to the point of
failure or to a specific point in time. This recovery model provides the best protection of your data, but requires more time to make the backup and restore.
With the Full Recovery model, all operations, including bulk operations such
as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged. The
backup strategy for this recovery model consists of the full database backups
(maybe with some differential backups) and transaction log backups. If the
current transaction log file for the database is available and undamaged,
you should perform the following steps to restore the database to the point
of failure:

  1. Back up the active transaction log.
  2. Restore the most recent full database backup without recovering the database.
  3. Restore the most recent differential backups, if it exists.
  4. Restore each transaction log backup created since the most recent full
    database backup (or the most recent differential backups, if differential
    backups exist) in the same sequence in which they were created without
    recovering the database.

  5. Apply the transaction log backup created in Step 1, and recover the database.

For example, to perform the Full Recovery model for the pubs database at the following backup intervals:

  1. Make the full database backup of the pubs database on Sunday at 1 AM.
  2. Make the differential backups of the pubs database on Monday at 1 AM
    and on Tuesday at 1 AM.

  3. Make the transaction log backups of the pubs database on Monday at 2 AM
    and on Tuesday at 2 AM.

So, if the primary data file of the pubs database was damaged on Tuesday
at 3 AM, you should recover the pubs database by using the following steps:

  1. Back up the active transaction log.
  2. Restore the full database backup from Sunday 1 AM.
  3. Restore the differential backup from Tuesday 1 AM.
  4. Restore the transaction log from Tuesday 2 AM.
  5. Apply the transaction log backup created in Step 1, and recover the database.


— Create a full database backup of the pubs database on Sunday at 1AM
BACKUP DATABASE pubs TO pubs_back_db WITH INIT
GO
— Time elapses.
— Create a differential backup of the pubs database on Monday at 1AM
BACKUP DATABASE pubs TO pubs_back_db WITH DIFFERENTIAL
GO
— Time elapses.
— Create a transaction log backup of the pubs database
— on Monday at 2AM
BACKUP LOG pubs TO pubs_back_log WITH INIT
GO
— Time elapses.
— Create a differential backup of the pubs database on Tuesday at 1AM
BACKUP DATABASE pubs TO pubs_back_db WITH DIFFERENTIAL
GO
— Time elapses.
— Create a transaction log backup of the pubs database
— on Tuesday at 2AM
BACKUP LOG pubs TO pubs_back_log
GO

— Time elapses.
— The primary data file of the pubs database was damaged
— on Tuesday at 3 AM
— Back up the active transaction log.
BACKUP LOG pubs TO pubs_back_log
GO
— Restore the full database from Sunday 1 AM without recovering
RESTORE DATABASE pubs FROM pubs_back_db WITH NORECOVERY
GO
— Restore the differential backup from Tuesday 1AM without recovering
RESTORE DATABASE pubs FROM pubs_back_db WITH FILE = 3, NORECOVERY
GO
— Restore the transaction log backup from Tuesday 2AM with recovering
RESTORE LOG pubs FROM pubs_back_log WITH FILE = 2, NORECOVERY
GO
— Apply the last created log backup and recover the database
RESTORE LOG pubs FROM pubs_back_log WITH FILE = 3, RECOVERY
GO

Bulk-Logged Recovery model

The Bulk-Logged Recovery model provides better performance in comparison
with the Full Recovery model because the SELECT INTO, Bulk load operations,
CREATE INDEX and WRITETEXT, UPDATETEXT operations are minimally logged.
Full and Bulk-Logged Recovery models are similar, and you can switch between
them easily. The Bulk-Logged Recovery model does not allow the database to
be recovered to a specific point in time. Under this recovery model the
transaction log backups capture both the log and the results of any bulk
operations performed since the last backup, so it is not necessary to
perform a full database backup after bulk copy operations complete.

»


See All Articles by Columnist
Alexander Chigrik

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles