Backup Types Supported by Recovery Models
The following table illustrates the types of backups that are available for each recovery model.
| Recovery Model | Full database backup | Differential backup | Transaction log file backup | File/filegroup backup |
|---|---|---|---|---|
| Simple | Required | Optional | Not allowed | Not allowed |
| Full | Required | Optional | Required | Optional |
| Bulk-Logged | Required | Optional | Required | Optional |
When you create a database backup, the backup operation copies only the data in the database to the backup file; it does not copy unused space in the database. Because the database backup contains only the actual data in the database—no empty space—the database backup is likely to be smaller than the database itself. An estimate of the size of the database backup can be determined using the sp_spaceused system-stored procedure; the reserved value indicates the estimated size.
Microsoft SQL Server truncates the transaction log file when backing up this file. Therefore, when you are creating database backups, it is recommended that you back up the transaction log file automatically every time a checkpoint occurs in the database. This prevents the transaction log file from becoming full, which would require the transaction log file to be truncated manually, which could result in data loss.
If you are producing only database backups, the backup interval should be long enough to keep the backup overhead from affecting production work, yet short enough to prevent the loss of significant amounts of data. Databases that do not contain critical data and have few modifications can be backed up on a weekly or biweekly basis. Data that is more critical or more volatile may need to be backed up daily, or even more frequently. Some databases that are usually read-only may need to be backed up only after a periodic refresh with new data.
It is also prudent to have more than one backup of the database. It is recommended that you maintain a rotating series of backup media, so that you have two or more versions of the database from which you can restore. This allows you to address situations in which a user may make some incorrect modifications that are not detected for some time, or to fall back to an earlier backup if backup media is damaged.
Copyright © 2005 Microsoft Corporation.
All rights reserved.