Backup and Restore Operations
Microsoft® SQL Server™ supports various types of backups to be used separately or in combination. The recovery model you choose will determine your overall backup strategy, including the types of backups available to you. For more information, see Designing a Backup and Restore Strategy and Using Recovery Models.
The following table illustrates the types of backups that are available for each recovery model.
|
Model |
Backup Type | |||
|---|---|---|---|---|
Database |
Database differential | Transaction log |
File or file differential | |
| Simple | Required | Optional | Not allowed | Not allowed |
| Full | Required (or file backups) |
Optional | Required | Optional |
| Bulk-Logged | Required (or file backups) |
Optional | Required | Optional |
Backups are created on backup devices, such as disk or tape media. With SQL Server, you can decide how you want to create your backups on backup devices. For example, you can overwrite outdated backups, or you can append new backups to the backup media. For more information, see Managing Backups.
Performing a backup operation has minimal effect on running transactions, so backup operations can be run during normal operations.
Note Creating or deleting database files is not possible when the database or transaction log is being backed up. If you attempt to create or delete a database file while a backup operation is in progress, the create or delete will fail. If you attempt to start a backup operation while a database file is being created or deleted, the backup operation will wait until the create or delete is completed or the backup operation times out.