AOMEI Cyber Backup
SQL Maintenance Plan | How to Automate Backups and Delete Old Backups
Creating Maintenance Plan is one of the most common ways to back up SQL Server databases. Here I will introduce you how to use Maintenance Plan to create automatic backup tasks and old backup deletion task.
By Crystal / Updated on October 26, 2023
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 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 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…
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 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.
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.
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 Cyber Backup can be a very good alternative.
AOMEI Cyber Backup allows you to centrally control SQL server databases within LAN from a single computer. You can backup SQL Server database to remote location, and monitor and manage the backup tasks from a centralized console. It comes with an intuitive GUI that enables you to operate without expertise. Besides SQL database, it supports other data types such as virtual machine backup.
This software works with Windows 7 and Windows Server 2008 R2 onwards, and it allows you to back up SQL Server 2005 - 2022.
You could click the following button to use AOMEI Cyber Backup for 30 days trial.
Schedule Auto Backups and Deletion for SQL Server Databases
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.
8. Click "Start Backup" to complete the backup task. It will automate SQL server database backups.
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.
