SQL Server database scheduled automatic backup

This article was reproduced from: http://www.cnblogs.com/zhangq723/archive/2012/03/13/2394102.html

The Breeze seeks the dream

In SQL Server, for data security reasons, you need to back up the database on a regular basis. And the backup database is usually in the early hours of the basic no database operation,

So it's impossible to ask the administrator to back up the database 1 o'clock every night. 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 in which you create a new job, add 1 backup steps in the job, type is a T-SQL script, and then enter the following SQL statement in the command, the statement

A full backup of the database TestDB1 is implemented, the backup file is in the C backup folder, and the file name is the date string testdb1+ the backup. Bak.

[Email protected] (250)
[Email protected]= ' C:\Backup\TestDB1_ ' +
CONVERT (varchar), GETDATE (), () + '. Bak '
Backupdatabase[testdb1]to
[Email protected]
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 seldom used in general. While it's easy to use the SQL job to perform a backup of the SQL script, there are some people who feel uncomfortable about writing the backup script at least.

Is there a simpler, more lazy way to implement automatic scheduled backups of a 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 maintenance SSIS package with a few clicks of a visual operation.

It is then still running in the way of SQL Server jobs. 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 database needs to be backed up, because the data in the database a lot of data file is very large, if every time a full backup then the hard disk occupies a large

Space, and backup time is long, and maintenance is cumbersome. For this we can use a full backup + differential backup, every Sunday a full backup, every night to do a bad

Different backups. 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 files with the last full backup and the differential backup

To restore the database at a time of 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, as our plan includes 2 parts: full backup and differential backup, this 2 part of the execution plan is

Not the same, one is executed once a week, the other is executed once a day, so select the "separate schedule for each task",

(3) Click the "Next" button, select the maintenance task, this is the task that can be performed in the maintenance plan, if you want to perform the task is not here, it is still no maintenance plan to

Do, write your own 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 task appears in the list, but we do not adjust its order, that is because in step 2

We chose to plan each task individually, so these 2 tasks are independent and have no sequencing 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 text

is saved in the C-disk backup directory, the extension is Bak, we can check "Verify backup integrity" For security reasons, or not. Compression is provided in the SQL2008.

New features that make 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 set 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 except Sunday each

Days for differential backups,

(7) Click the "Next" button to go to the Select report option, where we can write the execution report of this maintenance plan to a text file, or the report can be sent by e-mail to the management

Rapporteur If you want to send a message, you need to configure Database Mail for SQL Server, and also set up an operator in SQL Server Agent, about the configuration network for mail notification operators

More on the above, 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 "Jobs", right-click Dbbackupplan.subplan_1, select

The job start step system executes the job immediately, and after the system has run, we can have a backup file of our full backup under the C:\Backup folder.

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 established in Sunday, we must make a full backup before making this maintenance plan, and the backup must be kept at least until next week, otherwise

When there is 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 previously created

Full backup + differential backup maintenance plan Now we 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

To modify the maintenance plan, you can do 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, drag the Purge history task from the Toolbox to the Plan panel, and then click Back in the panel

Database (complete) component, the system displays a green arrow and drags the green arrow onto the Purge 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 are going 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. (Note: I do in the SQL2008 Chinese version of the virtual machine in the time

Once the maintenance plan is modified, save the time of the error 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

The text version is right inside. ）

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.

SQL Server Database scheduled automatic backup (RPM)