Go SQL Server 2012 notes Sharing-25: Configuring Backup Maintenance Plans

This article turns from http://543925535.blog.51cto.com/639838/1427529

In the daily SQL maintenance, there are many tasks that need to be repeated periodically, we are not likely to do manual operations, such as backup jobs, rebuild maintenance index, database collation and so on.

Take backup as an example, we usually put the time period of database backup at night, then we can make the backup job repeat periodically at night by creating the related maintenance plan of the backup.

The whole process is not particularly complicated, let's take a look at the whole implementation process.

==================================================================

Expand administration-Maintenance plan, right-click to select Maintenance Plan Wizard.

Go to the SQL Server Maintenance Plan Wizard.

An easy-to-identify name for the maintenance plan.

Tick "separate schedule for each task".

In the Select Maintenance task interface, tick "backup Database (Full)", "Backup Database (diff)", "Backup Database (Thing Log").

Select the sequential interface to perform the task, and leave the default.

After clicking Next, the Maintenance Plan Wizard will first let us customize the scheduled tasks for the full backup in the order above.

We first select the database to be fully backed up as DB01.

Then select backup type for disk backup, to use the backup device as "Fullbackup", as.

You can also tick verify the integrity of the backup.

If the backup file exists, we can choose either "Append" Backup or "overwrite" backup.

Then pull down the progress bar on the right, we configure the time of the full backup schedule execution, and select "Change".

The full backup schedule is set to execute at 0:00 every Sunday.

We then configured the maintenance plan for the differential backup in the same way as above, for example, selecting the backup device as Diffbackup and the backup time from Monday to Saturday 0:00.

Finally, the transaction log maintenance plan is configured with the same logic.

In the Select report options interface, we can configure the operation report for the maintenance plan.

If you have previously created an operator, we can also choose to send the report as an email.

After the summary interface confirms that there are no problems with the configuration, select Done.

After the creation is complete.

====================================================================

We can manually execute a maintenance plan to verify the configuration.

If there are more than one sub-plan under the maintenance plan, we cannot do it manually in the same way, otherwise we will get an error.

An error such as.

We need to locate the SQL Server Agent under "Jobs" to perform maintenance planning tasks:

Right-click the job you want to perform manually, and select job start step.

After successful execution,.

We can also get the script for the job by right-clicking the job and choosing "script as".

Gets the Create script to.

Finally, we right-click the job's properties to make a custom configuration for that job.

Go SQL Server 2012 notes Sharing-25: Configuring Backup Maintenance Plans