SQL Maintenance Plan | How to Automate Backups and Delete Old Backups

Why Choose Maintenance Plan for SQL Server Backup

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

A Backup Maintenance Plan covers all the basic needs for auto backup SQL Server database, 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 complete Maintenance Plan SQL Server backup. And if you are not familiar with the operations, you can also use the SQL Server Maintenance Plan Wizard to guide you through its setup.

📌Before you begin, please note that the SQL 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 Create Auto Maintenance Plan for SQL Server Backups

SQL Server provides built-in tools to automate backups. Using SQL Server Management Studio (SSMS), you can create a maintenance plan that schedules and manages backups, ensuring they run consistently and correctly.

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…

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.

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.

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

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.

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…

How to Use Maintenance Plan to Delete Old Backup Files Automatically

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.

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.

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.

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 Maintenance Plan SQL Server backup 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.

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.

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

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.

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

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.

Easier Way to Create Auto Backup for SQL Server Databases [Free Trial]

While SQL Server offers powerful tools for managing backups, the process can still be complex and time-consuming, especially as your database environment grows. And, SQL Maintenance Plan is not available on the Express version. This is where AOMEI Cyber Backup comes in.

AOMEI Cyber Backup is an intuitive, automated backup solution designed to simplify the process of managing SQL Server backups. Besides SQL database, it supports other data types such as virtual machine backup and centralized Windows PCs/Servers backup.

You could click the following button to use AOMEI Cyber Backup for 30 days trial.

Schedule Auto Backups and Delete Old Backups of SQL Server Databases [Step-by-Step]

You can easily backup SQL Server database to remote location, and monitor and manage the backup tasks from a centralized console. Here are detailed steps to automatically set up SQL Server backup and delete old backups using cleanup feature.

To properly use AOMEI Cyber Backup, you should note these prerequisites for Microsoft SQL backup:
▽ Computer with both AOMEI Cyber Backup Agent and Microsoft SQL Server installed
▽ Local disk or network share to store backup files

1. Access to "Source Device" > "Add Microsoft SQL". If the database exists and the version is supported, it will appear automatically. Otherwise, you can click "Add Microsoft SQL" > "Download proxy program", and install the program on the device with SQL Server installed. Then click "Already installed proxy" and select the proxies you want to add.

2. Click "..."-->"Authentication" to validate the database instance. You can choose "Windows Authentication" or "SQL Authentication". Enter the credentials and click "Verify".

3. Click "Backup Task" > "Create New Task" to backup your SQL databases.

4. Choose backup type as "Microsoft SQL Backup", and you could also try virtual machine Backup.

5. Enter a name for the backup task, then go to select the databases you want to backup and specify a location as a storage end.

6. Schedule SQL database backups to automate backup tasks:

✨Select backup methods as full/incremental/differential and specify the backup time as daily/weekly/monthly. Your SQL databases will be protected automatically and regularly.

7. Enable "Backup Cleanup" to delete old SQL backups automatically.

✨To save storage space, Backup Cleanup feature assists you in automatically removing previous backup versions according to rules. You can let AOMEI Cyber Backup clear up automatically after turning this option on, so you don't have to worry about backup failures because of full disk space.

8. Click "Start Backup" to complete the backup task. It will automate SQL server database backups.

With its user-friendly interface, AOMEI Cyber Backup makes it easy to set up and delete SQL Server backups, even for those with limited technical expertise.

Summary

Maintenance Plan is a very practical feature in SQL Server, which contains many functions to meet all the basic needs. For example, SQL Server auto backup every day and deleting old backups, as I introduced Maintenance plan SQL Server backup 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.

As a professional backup software, except for SQL Server backup I introduced above, this software also supports VMware backup and Hyper-V backup. Now with simple clicks, you can use one software to accomplish them all.