Table 14-1 lists backup strategies you may want to use. As you can see, these backup strategies are based on the type of database as well as the type of data. When planning a backup strategy, remember the following:
The master database stores important information about the structure of other databases, including the database size. Any time database information or structure changes, master may be updated without your knowing about it. For example, the size of most databases changes automatically, and when this happens master is updated. Because of this, often the best backup strategy for master is to schedule backups every other day and to rotate through several backup sets so that you can go back to several different versions of master if necessary.
You can use transaction logs to recover databases up to the point of failure and up to a point of work. To recover a database to a point of work, you must insert named log marks into the transaction log using BEGIN TRANSACTION WITH MARK. You can then recover to a mark in the log using RESTORE LOG WITH STOPATMARK or RESTORE LOG WITH STOPBEFOREMARK.
Table 14-1. Backup Strategies for System and User Databases
Database Type | Details | Strategy |
|---|---|---|
User | Recovery up to the minute | Run full backups twice a week, if possible. Use nightly differential backups and back up the recovery transaction log every 10 minutes during business hours. Do not use Truncate Log On Checkpoint, as this will make it impossible to recover some transactions. To improve backup restore speed, use multiple backup devices whenever possible. |
Recovery up to a point of work | Run full backups twice a week, if possible. Use nightly differential backups and back up the recovery transaction log every 10 minutes during business hours. Do not use Truncate Log On Checkpoint. Use named transactions to insert named marks into the transaction logs. To improve backup/restore speed, use multiple backup devices whenever possible. | |
Recovery up to the hour | Run full backups twice a week, if possible. Use nightly differential backups and back up the recovery transaction log every 30 minutes during business hours. Do not use Truncate Log On Checkpoint. To improve backup/restore speed, use multiple backup devices whenever possible. | |
Recovery of daily changes | Run full backups at least once a week. Use daily nightly differential backups and back up the changes transaction log every four hours during business hours. Do not use Truncate Log On Checkpoint. | |
Read-only | Schedule a full backup of the database every 30 days and supplement this with an additional full backup whenever the database is modified. | |
System | distribution | Available when you configure replication and the server is acting as a distributor. Schedule full backups after snapshots. With transactional replication, schedule regular log backups. |
master | Run full backups immediately after creating or removing databases, changing the size of a database, adding or removing logins, or modifying server configuration settings. Do not forget to maintain several backup sets for master. | |
msdb | If you schedule jobs through the SQL Server Agent, back up this database regularly because this is where the job schedule and history is maintained and backup history is stored. | |
model | Treat like a read-only database. | |
publication | Available when you configure replication and the server is acting as a distributor. If you do not perform log backups, the publication database should be backed up whenever a setting relevant to replication is changed. | |
subscription | Available when you configure replication and the server is acting as a subscriber. Subscription database backups should be no older than the shortest retention period of all publications to which the subscriber subscribes. | |
tempdb | Normally does not need to be backed up. This database is recreated each time you start SQL Server. |