- Accidental DBA
- Posts
- SQL Server Maintenance Plans
If you're a DBA, sysadmin, IT manager, or Accidental DBA, you’ve probably seen SQL Server’s built-in Maintenance Plans.
They live right there in SSMS under the "Management" node, quietly offering to take care of your backups, index maintenance, integrity checks, random T-SQL tasks and more.
They look simple. They are simple. But that doesn’t mean they’re always the best solution.
What Maintenance Plans Can Do
Microsoft added Maintenance Plans to make basic tasks like backups accessible, especially in environments without a dedicated DBA.
The wizard-driven interface lets you:
Schedule Full, Differential, and Transaction Log backups
Perform index maintenance
Run DBCC CHECKDB
Execute basic cleanup tasks
Run T-SQL commands as part of the “flow”
And it all runs under SQL Server Agent so you can automate with just a few clicks.
What Maintenance Plans Can’t Do Well
Ease of use comes at the cost of flexibility.
Here’s where they fall short:
Limited control: You can’t fine-tune logic or dynamically skip steps based on conditions. Not without a lot of fiddling around in the SSIS canvas at least
LOTS of clicking, dragging, dropping, Googling, etc. if you are new to MPs. The Wizard will make some basic decisions for you.
Logging is basic: Failures often go unnoticed unless you’re checking manually. If a MP job fails, the reason is in the MP history, not the job history. Makes perfect sense.
Weird defaults: If you choose to create an index rebuild plan, it defaults to 30% or more fragmentation, and 1000 PAGES, that’s a LOT of time spent on teeny tiny 8MB indexes. Unless a page isn’t 8KB anymore.
If you're working in a mission-critical or highly regulated environment, these gaps can cause trouble.
They’re Not Useless
Don’t get me wrong. Maintenance Plans have their place.
Especially if you’re:
Running one SQL Server instance with a couple of databases
Trying to get any backups in place after years of neglect. Any backup is better than no backup...but that’s a different post
Buying time until a better strategy is in place
Step-by-Step: How to Create a Full Backup Maintenance Plan
Let’s walk through the simplest case: backing up all user databases once a day.
1. Launch the Wizard
In SSMS, expand Management
Right-click Maintenance Plans
Choose Maintenance Plan Wizard (grayed out? Update your SSMS)
2. Name & Schedule the Plan
Click Next on the welcome screen
Name your plan (e.g., Nightly Full Backup)
Choose Single schedule for the entire plan
Click Change to set the schedule:
Frequency: Daily
Time: 2:00 AM (or another low-traffic time)
Recurs every: 1 day
Click OK, then Next
3. Choose Task Type
Check only Back Up Database (Full) → Next
4. Configure Backup Task
Databases: Select All user databases (or hand-pick)
Backup to: Disk → Choose or create a folder (e.g., D:\SQLBackups\)
URL is an option, for cloud storage.
Local backups are rarely a good idea. If the server dies, you’ve lost everything.
Optional:
Create a sub-directory per database
Set backup expiration
Enable checksum
Click Next
5. Reporting (Optional)
Save report to a text file or enable email notifications
The default is the same directory your SQL ERRORLOGs are living in.
6. Finish
Review the summary
Click Finish to create and schedule the plan
Done. Backups will now run on schedule, and you’ve taken a first step.
But now you need to repeat that process for all the other maintenance tasks (Log backups, stats maintenance, CheckDB, etc.)
There’s a Better Way
Once you're past the basics, most SQL Server professionals recommend moving on from Maintenance Plans. Here’s what they use:
Ola Hallengren’s Maintenance Solution
Free, flexible, and widely used in the SQL community.
Modular design
Intelligent scheduling
Excellent logging
Works with the SQL Agent
VERY simple setup. Please run this against a ‘DBA’ database, not master or msdb.
SQL Server Agent Jobs with Custom T-SQL
More setup time, but gives you full control over backup paths, logging, and error handling.
Third-Party Tools
If budget allows, options like Redgate SQL Backup or Idera SQL Safe Backup can offer robust UIs, centralized management, and alerts.
The Bottom Line
Maintenance Plans are training wheels.
They’ll get you moving, but they’re not built for high-speed, high-traffic, or high-stakes environments.
If you’re serious about protecting your data, build a better backup strategy. But if you're just getting started and need a win?
New Pocket DBA® clients get the first month FREE!
Book a call, and mention “Newsletter”
SQL TidBits:
Maintenance plans use the SSIS framework, which is installed with SSMS up to v20. V21.2.5 requires you to select it during install. The SSIS service does not need to be installed on the SQL Server.
Links:
Kevin goes on a bit of a rant about people posting garbage for clicks.
Please share with someone that is trying to copy and paste maintenance plans between servers and wonders why it isn’t working right.
Reply