SQL Server 2008 Backup

Backup Types

SQL Server 2008 allows you to create four different types of backups:

  • Full Backups
  • Differential Backups
  • Transaction log Backups
  • Filegroup Backups

Full Backups

A full backup captures all pages within a database that contain data. Pages that do not

contain data are not included in the backup.

Because it is more common to back up a database than to restore one, the backup engine

is optimized for the backup process. When a backup is initiated, the backup engine grabs

pages from the data files as quickly as possible, without regard to the order of pages. Because

the backup process is not concerned with the ordering of pages, multiple threads can be used

to write pages to your backup device.

Because changes canbe made to the database while a backup is running, SQL Server needs to be able to accommodate the changes while also ensuring that backups are consistent for restore purposes. To ensure both concurrent access and backup consistency, SQL Server performs the steps of the backup procedure as follows:

1. Locks the database, blocking all transactions

2. Places a mark in the transaction log

3. Releases the database lock

4. Extracts all pages in the data fi les and writes them to the backup device

5. Locks the database, blocking all transactions

6. Places a mark in the transaction log

7. Releases the database lock

8. Extracts the portion of the log between the marks and appends it to the backup

The only operations that are not allowed during a full backup are

  • Adding or removing a database file
  • Shrinking a database

The only two parameters required for a backup are the name of the database and the

backup device. When you specify a disk backup device, a directory and a fi le name can

be specifi ed. If a directory is not specifi ed, SQL Server performs a backup to disk and writes

the file to the default backup directory configured for the instance. Although most backups

are written to a single disk file or a single tape device, you can specify up to 64 backup

devices. When you specify more than one backup device, SQL Server stripes the backup

across each of the devices specified.

Enhanced by Zemanta

One response to this post.

  1. […] SQL Server 2008 Backup […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: