Backups are used to restore the database to a previous state if something goes wrong, such as system crashes, accidental data deletion, or hardware failures. Database backups can also be used for routine administrative tasks, such as copying a database from one server to another.
SQL Server supports several backup types, each designed for different recovery scenarios and storage requirements. A typical recovery operation starts with a full backup, followed by regular differential and transaction log backups. This combination minimizes restore time while maintaining the ability to recover to a specific point in time.
| Backup Type | Description | Usage |
|---|---|---|
| Full | Backs up all the data in a specific database or set of filegroups or files, and logs to ensure data recovery. | Serves as the foundation for differential and log backups. Usually performed weekly or daily. |
| Differential | Backs up only the data that has changed since the last full database backup. | Reduces backup size and time. Commonly scheduled between full backups. |
| Transaction log | Records all transactions (both DML and DDL) that occurred in the database, enabling point-in-time recovery. Available only under the full or bulk-logged recovery models. |
Enables point-in-time recovery and minimizes data loss. Usually scheduled frequently, for example, every 15 minutes. |
| Copy-only | Creates a full or log backup that does not affect the standard backup sequence. | Used for ad-hoc backups or testing without disrupting scheduled backups. |
| File | Backs up each file independently instead of backing up the entire DB. | Used for very large databases or partial restore operations. |
| Filegroup | Saves all the files within a particular filegroup. | Used for very large databases or partial restore operations. |
| Partial | Backs up the primary filegroup and any read-write filegroups, excluding read-only ones. | Used when some filegroups do not change often. |
| Tail-log | Backs up logs that aren’t backed up yet. | Used as the final step before restoring a database after a crash. |
The database recovery model determines which backup types are available and how transaction logs are handled.
| Recovery model | Supported backup types | Restrictions |
|---|---|---|
| Simple | Full, Differential | Transaction log backups are not supported. The log is truncated automatically. |
| Full | Full, Differential, Log, Copy-Only | Supports point-in-time recovery. |
| Bulk-Logged | Full, Differential, Log | Similar to Full, but with minimum log bulk operations. |
You can back up a database while it’s online. However, the backup operation may fail in the following scenarios:
ALTER DATABASE statement runs with the ADD FILE or REMOVE FILE options.