By Alison / Last Updated April 19, 2022

Why Choose SQL Server Maintenance Plan?

In order to protect our data in SQL Server databases from accidental loss, we need to back up one or more databases regularly. Generally, Creating Maintenance Plan is one of the most common ways to schedule automatic backups.

A Backup Maintenance Plan covers all the basic needs for backing up SQL databases, and requires no script. Complementary to right-clicking on database in Object Explorer and backing up directly, you can use Maintenance Plan to achieve the following practical functions:

Backing up multiple databases simultaneously

Scheduling Automatic backup tasks

Deleting old backups periodically

In this article, I will introduce you the specific steps to do this. And if you are not familiar with the operations, you can also use the Maintenance Plan Wizard to guide you through its setup.

Before you begin, please note that the Maintenance Plan and SQL Server Agent are not available on Express version. If you are using the Express version, you can create a batch file, or using professional backup software to automate SQL Server backup.

How to Schedule Automatic Backups using Maintenance Plan?

1. Launch SQL Server Management Studio (SSMS) and connect to your instance. Right-click SQL Server Agent and select Start to enable this feature.

2. Open up Management menu, right-click Maintenance Plans and select New Maintenance Plan…

New Maintenance Plan

3. Click Toolbox on the upper bar or next to Object Explorer, select Back Up Database Task and drag it into the blank on the right.

Double-click the Task to Edit it.

Back Up Database Task

4. In the pop-up window:

  • Choose the Backup type of Full, Differential or Transaction Log Backup.
  • Check single or multiple databases you want to back up in Database(s). Click OK to finish the settings.
  • Specify the destination location in Back up to section. Select to Back up databases across one or more files, or Create a backup file for every database as need.
  • If you want to Overwrite the existing backups you can select it in If backup files exist.
  • You can also Verify backup integrity and Set backup compression if you need.

Note:
Copy-only Backup: This option is used to break the differential chain.
Backup set will expire: this option is used to specify after how many days the backup can be overwritten by another backup.

Set the backup type and path

5. Click Subplan Schedule (calendar icon) on the upper bar to schedule this backup task.

Subplan Schedule

6. Arrange the Schedule type, Frequency, Daily Frequency and Duration in the pop-up window. You can select to back up the databases daily, weekly, or monthly.

Click OK to save the settings.

New Job Schedule

7. Then you can use windows shortcut Ctrl+S to save the changes.

Right-click Maintenance Plans and select Refresh, you can see the created plans are stored in Maintenance Plans folder. You can right-click the plan and select Modify to change the settings.

8. To enable this plan, you need to find it in SQL Server Agent > Jobs menu. Right-click it and select Start Job at Step…

Start Job at Step

How to Auto Delete Old Backup Files Using Maintenance Plan?

To save disk space, you can also use SQL Server Maintenance Plan to delete old backup files periodically, by creating a SQL Maintenance Cleanup Task. Here are the specific steps:

1. Launch SSMS and enable SQL Server Agent. Right-click Maintenance Plan and select New Maintenance Plan…

2. Click Toolbox next to Object Explorer, select Maintenance Cleanup Task and drag it into the right blank. And then Double-click the task to Edit it.

Maintenance Cleanup Task

3. Select Backup files as the deleting files.

Select Search folder and delete files based on an extension, and click to specify the location where the backups are stored. Input bak in File extension.

If you want to delete the transaction log backups you can input trn in File extension.

In File age, specify after how many days the older files will be deleted. Click OK to save the settings.

Deletion Schedule

4. Click Subplan Schedule (calendar icon) on the upper bar to schedule this task. Arrange the Schedule type, Frequency, Daily Frequency, and Duration in the pop-up window. Click OK to save the changes.

Schedule

5. Now you can use shortcut Ctrl+S to save this task.

As usual, to enable this plan, you need to find the plan in SQL Server Agent > Jobs, right-click it and select Start Job at Step…

How to use SQL Server Maintenance Plan Wizard?

If you are not familiar with the above operations, you can also choose to use the Maintenance Plan Wizard to guide you through the setup. They both operate on the same principle, while the Maintenance Plan Wizard provides a more intuitive interface, with clear step-by-step instructions to help you complete the setup more easily and quickly.

Here are the specific steps:

1. Launch SSMS and connect to your instance, right-click SQL Server Agent to Start this feature.

2. Open up Management menu, right-click Maintenance Plans and select Maintenance Plan Wizard.

Maintenance Plan Wizard

3. In the pop-up window, Name this plan and write a Description.

To schedule automatic tasks, you need to select Single schedule for the entire plan or no schedule, click Change… to set the Type, Frequency, and Duration in the pop-up window. Then you can click Next.

Schedule plan

4. Select one or more tasks to perform. For example: Back Up Database (Full) and Maintenance Cleanup Task.

Select backup type

5. Select the order for the tasks to execute. Then click Next to set up each of them separately. The setting page is the same as above ways.

Click Next to save the settings.

Select the order

6. Specify where to save the report of this plan. Then click Next.

Report path

7. In this page you can Verify the choices you made, if you want to change them you can click .

Then click Finish to perform this plan.

Verify the choices

One Effective Alternative to Auto Backup SQL Server Database

SQL Maintenance Plan, while useful, is not available on the Express version. So, if you are using the Express version, or if you want an easier, faster, and more efficient way to automatically back up your database regularly, AOMEI Centralized Backupper Database can be a very good alternative.

AOMEI Centralized Backupper Database allows you to centrally control all laptops, workstations, and servers within LAN from a single computer. You can remotely back up multiple SQL Server databases on other computers, and also monitor the network speed and available disk space of these computers to avoid failures.

Once created each backup task will be listed separately for further operations, such as changing the settings and restoring. When restoring, backup files will be automatically organized into a list by backup type and time. And you can also choose to restore them to your local sever, and other computers within LAN.

For enterprises, it is a great solution to save the time of operating individually on different computers.

Here’s a 30-day free trial for you:

Download Free Trial Win PCs & Servers
Secure Download

To properly use AOMEI Centralized Backupper Database, especially if you want to manage multiple computers within LAN, you need to go to the Computers page first. Click Install client program on the upper bar to download Agent on client computers manually or remotely.

Install Client Program

For those computers which download client program manually, you need to request control over them for further management and operation.

Click Computers > Uncontrolled Computers to check single or multiple client computers, and click Request Control on the upper bar. A pop-up window will show on their computers, once all permissions are received, you can start backup and restore whenever you like.

Request Control

How to Automate Backups of Multiple Databases on Other Computers

1. Click Tasks > New Task and select SQL Server Backup to create a new backup task. There are 3 steps you need to follow.

SQL Server Backup

2. Step 1, click Add Computers to add the controlled client computer you want to back up with.

Add Computers

3. Step 2, click Add, you can select single or multiple SQL instances and databases you want to back up. Click OK to the next step.

Select databases

4. Step 3, select a location as the destination path. You can click Add Storage to add a share or NAS path as a storage end.

Select destination path

Besides, click Settings you can enable encryption, email notification, and compression for backups. 

To further save the space occupied by excessive backups, it provides 2 compression levels, the higher the compression level you choose, the smaller the backup file will be.

Compression levels

5. Click Schedule next to Settings, you can select to run this task Once, Daily, Weekly, or Monthly. Each option you can select specific days and start time. You can also change it after.

Schedule settings

Click Advanced you can choose to perform Full or Differential Backup.

Advanced settings

After all these settings, you can click Start Backup to select Only create the task, or Create and execute the task. Once created, the tasks will be listed in Tasks separately for further edit or restore.

Start Backup

Summary

Maintenance Plan is a very practical feature in SQL Server, which contains many functions to meet all the basic needs. For example, scheduling automatic backups and deleting old backups, as I introduced in this article. However, it’s not available on Express version.

So, if you are using the Express version, or you want a more efficient way to centrally backup and restore multiple databases on other computers within LAN, or you need a higher compression level to save space, I recommend you use AOMEI Centralized Bakcupper Database Edition.

As a professional backup software, except for SQL Server backup I introduced above, this software also combines File Backup, System Backup, Disk Backup, Partition Backup and other functions, which requires multiple media in traditional ways. Now with simple clicks, you can use one software to accomplish them all.