Backup Concepts
118 Agent for Microsoft SQL Server Guide
■
If your database activity is high for a large size database using the Full or
Bulk-Logged Recovery model, we recommend the following pattern:
–
Full backup: once per week
–
Differential backup: once per day
–
Transaction Log backup: every 20 minutes
■
If your database activity is high for a large size database using the Simple
Recovery model, we recommend the following pattern:
–
Full backup: once per week
–
Differential backup: twice per day
Full Backups
A Full Database backup creates a copy of the entire database at the time the
backup command was executed, including all schema data, user data, Files and
FileGroups and part of the Transaction Log, regardless whether that data has
changed since the last backup. However, it is important to perform regular and
separate Transaction Log backups in order to capture the entire Log file and
truncate the log so it does not grow too large.
A Full backup can be ordered from the Backup Method selections of the Agent
Options, from the Global Agent Options, or from the Rotation options in the Job
Scheduler.
If you select a Full backup from the Agent Options dialog, you may refine the
backup job by setting Database Subset options. For example:
■
Combine a "Full" Backup Method with an "Entire Database" Database Subset
selection to back up all schema, data, Files and FileGroups, regardless if data
has changed.
■
Combine a "Full" Backup Method with a "Files and FileGroups" Database
Subset selection to back up all files from specific FileGroups in their entirety
or to isolate specific data files. If you use FileGroups to separate Read-Only
files from Read-Write files, this is one way to limit how often your Read-only
FileGroups are backed up.
■
Combine a "Full" Backup Method with a "Partial Database" Database Subset
selection to back up all files in all Read/Write FileGroups, including the
Primary FileGroup, regardless if data has changed.
