SQL Server 2008 Maintenance plan Implementation database backup experience

Best practices

(i) Backup plan

1 2:00:00 A full backup of the database in Sunday every week;

2 every week from Monday to Saturday daily 2:00:00 performs a differential backup of the database;

3 Daily log backups of the database between 8:00:00 and 23:59:59, every 1 hours;

4 The last Sunday of each month 1:00:00 perform a full backup of the database;

(b) plan to explain

1. Depending on the backup file, I created 4 files to store the backup files: (Medusa is the database name)

1) Medusa_full: For storing full backup files;

2) Medusa_diff: For storing differential backup files;

3) Medusa_log: Used for storing log backup files;

4) Medusa_save: for storing and keeping backup files;

The 4 folders above are one by one corresponding to the 4 steps of the backup plan;

(Figure1: Folder naming conventions)

2. Create a maintenance plan named Medusa, and create 4 sub plans for the following figure:

(Figure2: List of child plans)

3. Set retention time for purge history, purge maintenance:

1) Subplan_full: Keep full backup file for 4 weeks (one * 4 weeks = 4 files)

2) Subplan_diff: Keep differential backup file for 2 weeks (6 * 2 weeks = 12 files)

3) Subplan_log: Keep the log backup file for 1 weeks; (16 * 7 days = 112 files)

4) Subplan_save: Keep backup file for 1 years; (one * December = 12 files)

The above time strategy should be based on the database security level, disk space, business characteristics and so on, you can modify and adjust the actual situation;

4. The following Figure3, Figure4, Figure5, Figure6 are the specific settings of the effect map: