Backup policies allow you to define backup maintenance plans across multiple SQL Server instances in your enterprise. These instances can reside on one or more physical servers.
What is a backup policy?
A backup policy consists of a list of databases you want to back up, a set of backup operations to be performed on those databases, and a set of schedules according to which the backups will be performed. You can choose to create the associated jobs to run on a specific schedule, run on demand (execute the jobs manually from the Management Console), or you can choose to define your policy for monitoring purposes only. You can then monitor the status of each backup, all from a single point of contact in the Management Console.
How do you incorporate backup strategies in your policies?
Implementing a policy requires that you have a clear understanding of your backup strategy. To determine a backup strategy to use, consider the following recovery model requirements.
| Model | Full Backup? | Differential Backup? | Transaction Log Backup? | File or Filegroup Backup? |
|---|---|---|---|---|
| Simple Model | Required | Optional | N/A | N/A |
| Full Model | Required | Optional | Required | Optional |
| Bulk-Logged Model | Required | Optional | Required | Optional |
What constitutes a good backup strategy?
Consider using all four backup types to maximize your recovery and minimize your data loss. A basic backup strategy fulfills the following needs:
- Creation of regularly scheduled database backups.
- Creation of frequent differential backups between full backups.
- Creation of transaction log backups more frequently than differential backups.
Database backup creation depends on server activity and data sensitivity. Ensure you implement a strategy and create policies that back up both user databases and system databases.
How do backup policies help you?
Backup policies allow you to plan and schedule your SQL Server backup maintenance, as well as monitor its success and failures, all from a single point of contact at the Management Console. By allowing the application and scheduling of a set of backup operations across all of your SQL Server instances enterprise-wide, SQL Safe policies make updating your maintenance plans a quick and easy process.
To create backup policies, see create a backup policy.
To view the status of your policies, see view the status of all backup policies or view status of a specific backup policy.