Automate Backup and Maintenance Jobs Using Maintenance Plan in SQL Server


I will explain Automate Backup and Maintenance Jobs Using Maintenance Plan in SQL Server  in this article.


Maintenance Plan

You can create regular and continuous plans and jobs that will ensure the continuity of our databases and maintain and perform more efficiently via Maintenance Plan in SQL Server.

These plans and jobs are as follows.

  1. You can take the full, differential and transaction log backups of our databases on a regular basis with this tool.
  2. You can create and perform Index maintenance as Regular job
  3. You can perform Database Statistic update


All Tasks are as follows.


I will create a Daily Full Database Backup Job and Cleanup task. Job will take full database backup daily and clean backups older than 1 week automatically.

Right Click Maintenance Plans and Create New Maintenance Plan as follows.



Maintenance Plan Tasks GUI is opened. You can do drag and drop.


If you don’t know What is the Backup and how to take backup, you can read following article before this.

SQL Server 2017 Backup -1


Click Back Up Database Task to specify which database is chosen and how to take backup.


Select databases to take backup.



Specify Destination for Backup directory.




Set options for Backup like Compression and Encryption as follows.


Databases are selected, then you need to set Scheduler to start Job automatically.



You can set Scheduler As Daily,Weekly and Monthly and set startup time of Job as follows.



Backup Job is created as follows.


Create a Cleanup Task by double click Maintenance Cleanup Task in the ToolBox Section.

Attack Backup job to the Maintenance Cleanup task. Maintenance Cleanup Task will start after Backup job is completed.

Double click Maintenance CleanUp task to set Cleanup options.

Set related directory and file extension, then Set File age when files will be deleted.


Back Up Database Task, Maintenance Cleanup Task and Scheduler are completed as follows.


When you save this Maintenance Plan, Related Job will be created like following.




You can start this job manual for testing as follows.


You can create also Rebuild, Reorganize Index Task, Shrink Database, Update Statistics Task to automate these tasks with Maintenance Plan Tasks.



Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *