Automate Backup and Maintenance Jobs Using Maintenance Plan in SQL Server

Hi,

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.

https://ittutorial.org/sql-server-tutorials-microsoft-database-for-beginners/