SQL Server maintenance plan for database backup (strategy combat)

First, background

Previously wrote an article about backup: SQL Server maintenance plan to implement database backup, the above article using full backup and differential backup basically can solve the problem of database backup, but in order to protect the data more secure, we need to perfect our backup plan again;

The following article mainly joins the log backup, and the design of the frequency of backup and design naming norms and other issues to combat;

Second, Best Practices

(a) Backup plan

1) A full backup of the 2:00:00 execution database in Sunday of each week;

2) 2:00:00 performing a differential backup of the database from Monday to Saturday per week;

3) Perform log backups of the database every 1 hours between 8:00:00 and 23:59:59 every day;

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

(ii) Programme explanation

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

1) Medusa_full: For storing full backup files;

2) Medusa_diff: For storing differential backup files;

3) Medusa_log: For storing log backup files;

4) Medusa_save: For storing and reserving backup files ;

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

(Figure1: folder naming specification)

2. Create a maintenance plan named Medusa, and create 4 sub-plans such as:

(Figure2: Sub-schedule list)

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

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

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

3) Subplan_log: Keep 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 policy needs to be resolved according to the security level of the database, disk space, business characteristics, etc., you can modify and adjust according to the actual situation;

4. The following Figure3, Figure4, Figure5, and Figure6 are specifically set:

(Figure3: Sub-plan full)

(Figure4: Sub-plan diff)

(FIGURE5: Sub-plan log)

(Figure6: Sub-plan Save)

5. After you save the maintenance plan, the job list for Figure7 is generated in the SQL Server Agent job:

(Figure7:job list)

6. After a period of execution, a series of files are generated in each folder:

(figure8:full file)

(Figure9:diff file)

(Figure10:log file)

(Figure11:save file)

7. With these files, we can restore to a point in time data, the difference of data is one hours, for example, you are in 2012-12-11 15:48:00 the database, I can roll back to the nearest this time is: 2012-12-11 15:00:00

(Figure12: Restore full backup file)

(Figure13: Restore diff, log backup file)

When using SSMs restore, the system will help you to find the most recent backup files, and checked, to complete the above restore, according to our previous backup plan, we need files:

1) Full backup file: Barfoo.Medusa_backup_2012_12_09_020005_0612500.bak

2) Differential backup file: Barfoo.Medusa_backup_2012_12_11_020005_8354977.bak

3) Log backup file:

Barfoo.Medusa_backup_2012_12_11_080005_4808937.trn

Barfoo.Medusa_backup_2012_12_11_090005_4816881.trn

Barfoo.Medusa_backup_2012_12_11_100005_6543553.trn

Barfoo.Medusa_backup_2012_12_11_110005_7645233.trn

Barfoo.Medusa_backup_2012_12_11_120005_8434417.trn

Barfoo.Medusa_backup_2012_12_11_130004_9379977.trn

Barfoo.Medusa_backup_2012_12_11_140005_2044137.trn

Barfoo.Medusa_backup_2012_12_11_150005_2208329.trn

The restore step is to restore the full backup file first, then restore the differential backup file, then execute the log backup file in turn, so you can roll back to the point you want: 2012-12-11 15:00

Third, Precautions

1. Backup Schedule "Daily log backups of the database between 8:00:00 and 23:59:59 every 1 hours" Many people will ask: this is the difference between 8 hours of data do not log backup? In fact, I think so, in the early hours, most of the situation this time is running the timer processing data, if not the program has a bug, generally do not cause data errors need to recover, and this time the user Operation page is not frequent (personally think more data misoperation caused by improper operation), You can make some adjustments according to your business situation;
2. Backup Plan "The last Sunday of every month 1:00:00 perform a full backup of the database", which is done to minimize the impact of full backups and keep a full backup file, if you need to be more rigorous, you can replace it with "copy-only backup (COPY_ONLY) , which requires T-SQL to be backed up in a maintenance plan, which can have no effect on the log chain at all;
3. Can a maintenance plan be generated by a T-SQL script?

Sp_add_maintenance_plan

sp_delete_maintenance_plan_db

sp_add_maintenance_plan_db

Sp_delete_maintenance_plan_job

Sp_add_maintenance_plan_job

Sp_help_maintenance_plan

Sp_delete_maintenance_plan

SQL Server maintenance plan for database backup (strategy combat)