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 ScopesThe 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 TypesThe 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.