Its all about Database: Database Maintenance Plan

Hii guys, I hope you are doing great, today here I am going to explain you about the Database Maintenance Plan.

Database Maintenance Plan-

A database maintenance plan is a set of specific, proactive tasks that need to be performed regularly on databases to ensure their adequate performance and availability.

Uses of Maintenance plan-

The Maintenance Plan Wizard creates a maintenance plan that Microsoft SQL Server Agent can run on a regular basis. This allows you to perform various database administration tasks, including backups, database integrity checks, or database statistics updates, at specified intervals.

Advantages of database maintenance plan-

Keeps Companies Up to date.

Promotes Efficient Database.

Simplifies Data Recovery.

Protects Against Malicious Threats.

Saves Time.

Disadvantages of database maintenance plan-

Limited number of database maintenance options.

Lack of granularity.

Inability to run multiple tasks.

No scripting to other instances.

Bugs in some earlier versions of the Wizard.

How to create database maintenance plan-

Step 1- To create database maintenance plan, open SSMS and connect to the SQL Server instance expand management right click on maintenance plan select new maintenance plan.

Fig.1

Fig.2

Step 2- In the New Maintenance Plan dialog box, specify the name of the maintenance plan.

Fig.3

Step 3- In the Maintenance plan configuration plan, drag and drop Back up Database Task from the toolbox and double-click to Edit it.

Fig.4

Step 4- After that click on backup database task and take backup type full and take database all and after that go to destination tab select the path and click on sub-directory of each database.

Fig.5

Fig.6

Step 5-After that you have go to Options tab and set backup compression type and click on verify backup integrity and click ok.

Fig.7

Step 6- Now, to schedule the full database backup, click on the calendar icon.

Fig.8

Create a Maintenance Plan for Differential backups

Step 7- To create differential maintenance plan, we create the subplan inside the backup maintenance plan.

Step 8- In the General tab, chosen Differential from the backup type drop-down box and go to destination tab and select the specific path.

Fig.9

Fig.10

Step 9- In the Options tab, I have chosen the same configuration options that I used in the Database Full Backup maintenance plan.

Fig11

Step 10- Now, to schedule the differential database backup, click on the calendar icon.

Fig 12

Create a Maintenance Plan for Transaction Log backups

Step 11- To create Transaction log maintenance plan, we create the another subplan inside the backup maintenance plan.

Step 12- In the General tab, chosen Transaction Log from the backup type drop-down box and go to destination tab select the path for the T log backup and backup file extension to trn.

Fig 13