Use the SQL Server maintenance plan to implement regular automatic Database Backup

In SQL Server, regular database backup is required for data security considerations. The backup database is generally performed in the early morning when there are basically no database operations, so we cannot ask the Administrator to back up the database at every day. To implement regular automatic database backup, the most common method is to use jobs in the SQL Server proxy. Start the SQL Server Agent service, create a job in it, add a backup step in the job, type is T-SQL script, and then enter the following SQL statement in the command, this statement implements a complete Backup of the database TestDB1. The Backup file is in the Backup folder of disk C, and the file name is TestDB1 + date string of the Backup at that time. bak.

 

Declare @ name varchar (250)
Set @ name = 'C: \ Backup \ TestDB1 _ '+
Convert (varchar (50), getdate (), 112) + '. Bak'
Backup database [TestDB1]
DISK = @ name
With noformat, NOINIT,
NAME = n' TestDB1-full database backup ',
SKIP, NOREWIND, NOUNLOAD

After the creation steps are completed, the next step is to create a plan. The operation for creating a plan is very simple, and the interface is described in detail. I will not talk about it much. You can also configure alarms and notifications, but this is rarely used.

The method of executing SQL scripts in SQL jobs to back up data is simple, but at least the BACKUP script should be written. Some people may feel uncomfortable, but is it easier, what about the lazy method to achieve automatic and timed backup of the database? Yes, that is, the "Maintenance Plan ".

The maintenance plan is under the "manage" node in the object Resource Management of SSMS. Using the maintenance plan, you can create an SSIS package for database maintenance with just a few clicks through visual operations, and then run it through the SQL Server job. The essential difference between the maintenance plan and the backup method mentioned above is that the maintenance plan is an SSIS package with the T-SQL script above.

Suppose we now have a production system database that needs to be backed up. Because there is a lot of data in the database, and the data files are very large, if the full backup is performed every time, the hard disk occupies a lot of space, in addition, it takes a long time to back up data and is difficult to maintain. For this purpose, we can use the full backup + differential backup mode to perform a full backup every Sunday and a differential backup every night. Differential backup can reduce the size of Backup files and increase the backup speed, however, the disadvantage is that the last full backup file and the differential backup file must be used to restore the database at the time of differential backup. it is meaningless to separate the differential backup file.

Next I will explain how to implement full backup + differential backup through the maintenance plan:

(1) In the object Resource Manager of SSMS, right-click "maintenance plan" and select "maintenance plan wizard". The system will pop up the wizard window,

Here, the wizard has already told us what the maintenance plan can do. The last one is exactly what we need to execute database backup.

(2) Click "Next" to enter the select plan attributes window and enter the Plan Name. Because our plan includes two parts: Full backup and differential backup, the execution plans for these two parts are different. One is to execute once a week, and the other is to execute once a day. Therefore, select "separate plan for each task ",

(3) Click the "Next" button and select a maintenance task. This is the task that can be executed in the maintenance plan. If the task you want to execute does not exist here, you do not need to maintain the plan. Write your own SSIS package or SQL statement. All the tasks we want to execute are here. select these two tasks,

(4) Click "Next" to go to the page for selecting the maintenance task order. here we can see that the selected task appears in the list, but we cannot adjust the order, that's because in step 2 we chose to plan each task separately, so the two tasks are independent and there is no sequential order. If another option is selected at the time, the order can be adjusted here.

 

(5) Select "backup database (complete)" and click "Next". The system will go to the page that defines the complete backup task,

This interface is too long. I can't hide the taskbar and the scroll bar appears. Here we choose the database to be backed up and choose to create a backup file for each database, the file is stored in the Backup Directory of drive C with the extension of bak. For security reasons, we can select "verify Backup integrity" or choose not. The new features of compressed backup are provided in SQL2008, which makes the backup file smaller and the backup speed faster. Here we use compressed backup. Finally, select the execution plan. Here I choose to execute the plan at every Sunday evening.

 

(6) Click "Next" to go to the configuration page of the differential backup task. The operation is the same as that in the previous step, we plan to perform differential backup every day except Sunday,

(7) Click "Next" to select the report option. Here, we can write the execution report of this maintenance plan into a text file, or send the report to the Administrator by email. If you want to send an email, you need to configure the SQL Server database email. In addition, you also need to set the operator in the SQL Server proxy. The configuration of mail Notification operators is also described on the Internet, I will not go into detail here.

(8) Click "Next" to go to the "finish this wizard" page. The system lists the work to be done by the wizard,

(9) Click "finish". The Wizard will create the corresponding SSIS package and SQL job:

(10) after completion, refresh the object resource manager. We can see the corresponding maintenance plan and the job corresponding to the plan:

 

 

 

 

Now the maintenance plan has been created. I am eager to see how it works after execution. I don't need to wait until am. Under "job", right-click DbBackupPlan. subplan_1, select "job start step" and the system will immediately execute the job. After the system runs, we can have the complete Backup file under the C: \ Backup folder.

The above operations can be purely non-keyboard operations, without writing any script, just a few clicks.

It should be noted that if we did not develop the maintenance plan on Sunday, we must make a complete backup before developing the maintenance plan, and the backup should be retained at least until next week, otherwise, a problem occurs and we find that only the differential backup within a few working days is deleted, and the last full backup is deleted again.

In addition to the maintenance plan wizard, you can create a maintenance plan directly or modify the maintenance plan created by the opinion. Let's take modifying the maintenance plan as an example. For the complete backup + differential backup maintenance plan created earlier, we need to clean up the database backup once a week. After the complete backup is complete, we need to delete the backup one month ago. You only need to modify the maintenance plan as follows:

(1) Right-click our maintenance plan and select the "modify" option in the pop-up menu. The system will create a new option card to display the current maintenance plan.

The lower-left corner shows the available maintenance plan components, and the lower-right panel shows the process settings Panel of the maintenance plan, which lists the sub-plans of the plan.

(2) Select the Subplan_1 subplan, that is, the full weekly backup subplan, and drag the "Clear history" task from the toolbox to the plan panel, click the backup database (complete) component on the panel. a green arrow is displayed, dragging the green arrow to the clear history component,

That is to say, after the database is backed up successfully, the history clearing task is executed.

(3) Right-click the "Clear history" task and select the "edit" option in the pop-up menu. The "Clear history" task settings window appears,

You can clear history logs or delete historical data on the hard disk. Here we want to delete the historical backup data four weeks ago, click "OK" back to the plan panel, we can see that the previous "Clear history" task on the Red Cross is gone. Click "save" to save the plan. (Note: Once I modify the maintenance plan in the SQL2008 Chinese Version virtual machine, an error will be reported when I save it. However, the English version of my local machine is normal, I don't know whether it is my VM or the Chinese version Bug. It is correct in the English version .)

After this modification, we do not need to manually delete the Database backups that have been created a long time ago. After the backup is completed, the system will delete the backup data that meets the conditions.

 

 

In addition, if you have used SSIS, you should know that a task is green when it is completed, and if it is a red arrow when it fails, we can also set it here. If the previous step fails, in the displayed dialog box, double-click the Green Arrow and select "failed" as the value of the constraint option.

In the maintenance plan, you can also set complex logic operations and execution processes, just like the SSIS design. After all, they are essentially designing SSIS packages.