One of the major advantages that enterprise-class databases offer over their desktop counterparts is a robust backup and recovery feature set. Microsoft SQL Server provides database administrators with the ability to customize a database backup and recovery plan to the business and technical requirements of an organization.

In this article, we explore the process of backing up data with Microsoft SQL Server.

When you create a backup plan, you’ll need to create an appropriate mix of backups with varying [em]backup scopes[/em] and [em]backup types[/em] that meet the recovery objectives of your organization and are suitable for your technical environment.

Backup Scopes

The scope of a backup defines the portion of the database covered by the backup. It identifies the database, file(s) and/or filegroup(s) that SQL Server will backup. There are three different types of backup scope available in Microsoft SQL Server:

• Database backups cover the entire database including all structural schema information, the entire data contents of the database and any portion of the transaction log necessary to restore the database from scratch to its state at the time of the backup. Database backups are the simplest way to restore your data in the event of a disaster, but they consume a large amount of disk space and time to complete.
• Partial backups are a good alternative to database backups for very large databases that contain significant quantities of read-only data. If you have read-only filegroups in your database, it probably doesn’t make sense to back them up frequently, as they do not change. Therefore, the scope of a partial backup includes all files in the primary filegroup, all read/write filegroups, and any read-only filegroups that you explicitly specify.

• File backups allow you to individually backup files and/or filegroups from your database. They may be used to complement partial backups by creating one-time-only backups of your read-only filegroups. They may also play a role in complex backup models.

Backup Types

The second decision you need to make when planning a SQL Server database backup model is the type of each backup included in your plan. The backup type describes the temporal coverage of the database backup. SQL Server supports two different backup types:

• Full Backups include all data within the backup scope. For example, a full database backup will include all data in the database, regardless of when it was last created or modified. Similarly, a full partial backup will include the entire contents of every file and filegroup within the scope of that partial backup.
• Differential Backups include only that portion of the data that has changed since the last full backup. For example, if you perform a full database backup on Monday morning and then perform a differential database backup on Monday evening, the differential backup will be a much smaller file (that takes much less time to create) that includes only the data changed during the day on Monday.

You should keep in mind that the scope and type of a backup are two independent decisions made when creating your backup plan. As described above, each type and scope allows you to customize the amount of data included in the backup and, therefore, the amount of time required to backup and restore the database in the event of a disaster.