Replacing Maintenance tasks now using SSMS 22 - what to do with File operations

Question

Replacing Maintenance tasks now using SSMS 22 - what to do with File operations

terry999

SSCertifiable

Points: 5039
More actions
June 4, 2026 at 1:14 pm

#4860149
Unfortunately I'm using SSMS 22.
It didn't come with maintenance plans by default. I ran visual studio installer to install the extra components (SSIS, B1 and something else) so I could create tasks.
I can now create tasks but cannot see the toolbox to add tasks e.g. backup task. So can only create an empty plan with in SSMS. Wish I knew this earlier wouldn't have bothered getting VS installer running.
I read this is by design, maintenance tasks are being phased out.
To replace these plans with Agent jobs.
I can replace the backup/reindex tasks with TSQL scripts in SQL Agent jobs
But how to handle file maintenance e.g.
1. Delete old backup files
2. Delete text files for jobs
Should I use the undocumented SPs that maintenance plans use e.g.
EXECUTE master.dbo.xp_create_subdir N'G:\SQL Backups\MaintTasks\myNewDataBase'
EXECUTE master.dbo.xp_delete_file 0,N'G:\SQL Backups\MaintTasks',N'bak',N'2026-05-28T13:51:49',1
or
do add steps in powershell/cmdshell for file handling with its difficult error handling and reporting to SQLAgent.
I'm tempted to use the undocumented SPs for reliability/ease.
I want to avoid to have packages created in some flavour of Visual Studio or other environment for what are v.simple tasks.
- This topic was modified 2 weeks, 6 days ago by terry999.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ratbak Default port Points: 1434 More actions · Answer 1

https://ola.hallengren.com/sql-server-backup.html is a great, widely used solution, including cleanup of old files, driven by stored procedures that can be incorporated into jobs.

terry999 SSCertifiable Points: 5039 More actions · Answer 2

Thank you, I had seen these scripts mentioned elsewhere.

I wanted to stay on maintenance plans because when away my IT colleague with few SQL skills has managed to fix/diagnose issues with the simple GUI of maintenance plans.

Good to see these scripts use undocumented SPs, so if I use them and they change breaking jobs everyone using the Ola scripts will break too - solutions, well its MS so workarounds will be developed but much smarter people than me.

jasona.work SSC Guru Points: 50324 More actions · Answer 3

Honestly, because from the sound of your post you're just going to be creating Agent jobs to do the backups and using the undocumented scripts, I'd agree with ratback, use the Ola scripts.

I've been using them now for a couple years, and frankly, they're dang near bulletproof. You can have them write out logs to a folder on your server, so if there IS a problem, someone can look through the log for the failure and troubleshoot from there. All the times I've run into errors, I've gone into the log, found the failed database backup, and been able to sort out the problem from the error message.