SQL Server Database Maintenance Plan Creates a complete backup strategy



SQL Server Maintenance Plan Maintenance Plan is a very useful maintenance tool that can accomplish most of the database maintenance tasks, and by encapsulating these features, you can save a lot of code writing time. There is not a lot of information on the network.

Database backup is a very important part of daily operations, there are many methods of backup, the simplest way is through the Management Studio graphics interface, this method for the immediate backup requirements is the fastest.

You can also use SQL statements to be more flexible with custom automated backups directly through statements:

BACKUP DATABASE mydb to disk= ' D:\Backup\mydb

But sometimes backup operations tend to be more demanding and automated, especially in data warehousing systems, such as backups of a production system, which are typically required:

-Children's Day Full-time backups per week

-Weekday time one incremental backup per day

-All backups are automatically deleted after one months

If you are a very powerful coder, it is not difficult, but not simple, to implement this requirement through SQL programming, or. NET C # code, and PowerShell and so on. In fact, SQL Server has long considered the need for us, that is, through SQL Server's own maintenance Plan.

Maintenance plans can be found under Management in SQL Server Management Studio.

First, right-click this folder to create a new maintenance plan.

Take a name for the maintenance plan.

Maintenance Plan interface. For the requirements we mentioned above, the maintenance plan corresponds to three subplan. The system creates an empty Subplan by default.

What features are supported in Subplan? Let's click Toolbox under the View menu.

You can see the types of tasks supported under the maintenance plan (Friends familiar with SSIS may be familiar with this).

Drag the back up Database task to the right.

Double-click the back Up Database Task on the design interface to eject the backup design interface, first specifying which database to backup. Multiple selections are supported here, so it is convenient to maintain the entire server. Here we only demonstrate backing up the AdventureWorksDW2012 database.

Backup type Select Full complete. Then specify information such as the backup path.

After the full backup is created, create a subplan to complete the incremental backup setup.

After clicking Add Subplan, give the sub-plan a name.

Database or select AdventureWorksDW2012, the Backup type selects "diff". The other settings are the same as before, but if you consider separating the differential and full backups, consider setting up the backup path again.

The settings for full and differential backups are complete, and the next step is to empty the historical backup. Add a subplan here and drag the maintenance Cleanup task to the design interface.

Set the folder to be cleaned, point to the backup path that we set up just now, and then set the file at the bottom for how long it needs to be cleaned out, here to choose 1 Month.

Three steps set up, as a full-scale backup, differential backup and backup cleanup, ask for maintenance convenience, the three subplan renamed:

Next, set the run cycle for each step. As defined by previous requirements, weekly Children's Day full backups, weekdays a differential backup, and then automatically cleans up backups one months ago.

First click on the Schedule button of the first Subplan, set the full backup cycle setting as follows.

You then set the period for the differential backup.

The last file cleanup cycle.

Once set up, save, and the maintenance plan is set up.

In fact, SQL Server automatically generated the appropriate SSIS package and job based on what you just did, and you can see the corresponding three jobs generated under SQL Server Agent jobs.

By opening each job, you can see that the corresponding package is generated under SSIS under the job step.

At this point, the implementation of a complex backup task through a maintenance plan is described here. Perhaps this backup is not very comprehensive, but it is basically enough to meet most of the requirements scenarios, especially the backup of the Data Warehouse, and the complex backup tasks can also be implemented through maintenance plans.

SQL Server Database Maintenance Plan Creates a complete backup strategy