Use SQL Server maintenance plan to implement scheduled automatic backups of the database

In SQL Server, for data security reasons, you need to back up the database on a regular basis. While the backup database is usually in the early hours of the basic no database operation, so we can not ask the administrator every day to the night 1 o'clock to back up the database. The most common way to implement scheduled automatic backups of a database is to use jobs in SQL Server Agent. Start the SQL Server Agent service, and then create a new job in it, add 1 backup steps in the job, type is a T-SQL script, and then enter the following SQL statement in the command, which implements a full backup of the database TestDB1, the backup file is in the C-drive backup folder, The file name is the date string that was backed up by testdb1+. Bak.

DECLARE @name varchar (250)
Set @name = ' C:\Backup\TestDB1_ ' +
CONVERT (varchar), GETDATE (), () + '. Bak '
BACKUP DATABASE [TestDB1] to
DISK = @name
With Noformat, Noinit,
NAME = N ' testdb1-full database backup ',
SKIP, Norewind, Nounload

After the steps are created, the next step is to create the plan, the operation of creating the plan is very simple, the interface is very detailed, I will not say much. You can also configure alerts and notifications, but this is rarely used.

While it's easy to use the SQL job to perform a backup of SQL scripts, but at least it's time to write the backup script, some people feel uncomfortable, is there a simpler, more lazy way to implement automatic scheduled backup of the database? Yes, that's the "Maintenance Plan".

The "Maintenance plan" is under the "Administration" node in SSMs object resource management. With a maintenance plan, you can create a database-maintained SSIS package with a few mouse clicks by visualizing the operation, and then still running through the SQL Server job. The difference between the maintenance plan and the backup method mentioned earlier is that the maintenance plan is the SSIS package, which is the T-SQL script.

Suppose we now have a production system of the database needs to be backed up, because of the data in the database, the data file is very large, if the full backup every time the hard disk takes up a lot of space, and backup time is very long, maintenance is also very cumbersome. For this we can use a full backup + differential backup, a full backup every Sunday, a differential backup every night. Using a differential backup can reduce the size of the backup file while also increasing the speed of the backup, but the disadvantage is that you must use the file with the last full backup and the file of the differential backup to restore the database at the time of the differential backup, only the differential backup file is meaningless.

Let me take a look at how to implement a full backup + differential backup with a maintenance plan:

(1) In SSMs Object Explorer, right-click Maintenance plan, select Maintenance Plan Wizard, the system will pop up the wizard window,

The wizard has already told us what the maintenance plan can do, and the last "perform database backup" is exactly what we need.

(2) Click "Next" button, go to the Select Schedule Properties window, enter the name of the plan, because our plan includes 2 parts: Full and differential backup, this 2 part of the execution plan is not the same, one is executed once a week, and the other is executed once a day, so to select "Each task separate plan",

(3) Click the "Next" button, select maintenance Tasks, this is the task that can be performed in the maintenance plan, if you want to perform the task is not here, then do not use maintenance plan to do, write the SSIS package or SQL statement yourself. The tasks we are going to perform are here, select these 2 tasks,

(4) Click "Next" to go to the Select Maintenance task order interface, here we can see the selected tasks appear in the list, but we do not adjust their order, that is because in step 2 we chose to each task separately planned, so the 2 tasks are independent, there is no order to say. If you chose another option at the time, you can adjust the order here.

(5) Select "Back Up Database (full)" and click "Next" button, the system will go to the interface that defines the full backup task.

This interface is too long, I have hidden the taskbar is displayed, there is a scroll bar, here we select the database to be backed up, choose to create a backup file for each database, the file is saved in the C-disk backup directory, the extension is Bak, for security purposes, we can check "Verify backup Integrity", Of course, we can not choose. The new features of compressed backup are available in SQL2008, making backup files smaller and faster to backup, where we are backed by compression. Finally, I chose the execution plan, which I chose to do every Sunday night at 0 o'clock.

(6) Click "Next" button, go to the setup interface of the differential backup task, and the interface of the previous step is the same, the operation is the same, the plan here we can choose to do in addition to Sunday of daily differential backup,

(7) Click the "Next" button to go to the Select report option, where we can write the execution report of the maintenance plan to a text file, or the report can be emailed to the administrator. If you want to send a message, then you need to configure SQL Server Database Mail, and also set up SQL Server Agent in the operator, about the mail notification operator configuration on the network also speak more, I do not detail here.

(8) Click the "Next" button to go to the "Complete the wizard" interface, the system lists the work to be done by the wizard,

(9) Click the Finish button and the wizard will create the corresponding SSIS package and SQL job:

(10) After the completion, we refresh the Object Explorer, we can see the corresponding maintenance plan and the program corresponding to the job:

Now the maintenance plan is created, anxious to see how the effect after execution, do not have to wait until 12 o'clock in the evening, under the "job", right-click on the dbbackupplan.subplan_1, select the "Job start Step" system immediately execute the job, after the system has completed, we can be in C: \ The backup folder is backed up with the full backup file we made.

The above operation can be purely keyboard-free operation, without writing any script, just a few mouse points.

It is important to note that if we are not the maintenance plan developed in Sunday, then we must make a full backup of the maintenance plan, and the backup should be kept at least until next week, or there will be a problem, found that only a few days of differential backup, and the last full backup was deleted, it is depressed.

In addition to using the Maintenance Plan Wizard, we can either create a new maintenance plan directly or modify the maintenance plan created by the comments. Let's take the example of modifying the maintenance plan. For the pre-created full backup + differential backup maintenance plan, we now need to clean up the database backups once a week, after the full backup is complete, to remove the backup 1 months ago. Then we just need to modify the maintenance plan, the following:

(1) Right click on our maintenance plan, select "Modify" option in the pop-up menu and a new tab will be created to display the current maintenance plan.

The lower left corner is the available maintenance plan component, and the lower right panel is the maintenance plan's process settings panel, which is the list of sub-plans for the plan.

(2) Select the Subplan_1 sub-plan, which is the weekly full backup sub-plan, drag the Purge history task from the Toolbox to the Plan panel, then click the Back Up Database (full) component in the Panel, and a green arrow will appear, dragging the green arrow to the Clear history Component,

That is, after a successful full backup of the database, the Purge history task is then performed.

(3) Right click on the "Clear history" task, select "Edit" option in the pop-up menu, the system will pop up the Clear History Task Settings window,

You can either clear the history log or delete the historical data on the hard disk. Here we have to delete the historical backup data 4 weeks ago, click "OK" to go back to the plan panel, we can see the original "Clear history" task on the small Red Fork is missing. Click the Save button and the plan will be saved. (Description: I SQL2008 in the Chinese version of the virtual machine in the time when the maintenance plan is modified, save when the error is catastrophic failure, but I am the English version of the machine is normal, do not know whether it is my virtual machine problem or the Chinese version of the bug, anyway in the English version is right. ）

After this modification, we do not have to manually delete those long-ago database backup, the system after the completion of the implementation of the data will be deleted to meet the conditions of the backup.

In addition, if the person who used SSIS should know that a task is green at the time of completion, if it is a red arrow, we can also set up here, if the previous step failed, then what will be done, double-click the green arrow, in the Popup dialog box, select the constraint option to the value of "failed".

It is also possible to set up complex logical operations and execution processes in the maintenance plan, just like SSIS design, after all, they are in the process of designing SSIS packages.

Use SQL Server maintenance plan to implement scheduled automatic backups of the database