THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Maria Zakourdaev

Maintenance plans love story

There are about 200 QA and DEV SQL Servers out there. 

There is a maintenance plan on many of them that performs a backup of all databases and removes the backup history files.

First of all, I must admit that I’m no big fan of maintenance plans in particular or the SSIS packages in general. 

In this specific case, if I ever need to change anything in the way backup is performed, such as the compression feature or perform some other change, I have to open each plan one by one. This is quite a pain.

Therefore, I have decided to replace the maintenance plans with a stored procedure that will perform exactly the same thing.  Having such a procedure will allow me to open multiple server connections and just execute an ALTER PROCEDURE whenever I need to change anything in it. There is nothing like good ole T-SQL.

The first challenge was to remove the unneeded maintenance plans. Of course, I didn’t want to do it server by server.  I found the procedure msdb.dbo.sp_maintplan_delete_plan, but it only has a parameter for the maintenance plan id and it has no other parameters, like plan name, which would have been much more useful. Now I needed to find the table that holds all maintenance plans on the server. You would think that it would be msdb.dbo.sysdbmaintplans but, unfortunately, regardless of the number of maintenance plans on the instance, it contains just one row.


After a while I found another table: msdb.dbo.sysmaintplan_subplans. It contains the plan id that I was looking for, in the plan_id column and well as the agent’s job id which is executing the plan’s package:


That was all I needed and the rest turned out to be quite easy.  Here is a script that can be executed against hundreds of servers from a multi-server query window to drop the specific maintenance plans.

DECLARE @PlanID uniqueidentifier


SELECT @PlanID = plan_id

FROM msdb.dbo.sysmaintplan_subplans

Where name like ‘BackupPlan%’


EXECUTE msdb.dbo.sp_maintplan_delete_plan @plan_id=@PlanID


The second step was to create a procedure that will perform  all of the old maintenance plan tasks: create a folder for each database, backup all databases on the server and clean up the old files. The script is below. Enjoy.


ALTER PROCEDURE BackupAllDatabases

                                  @PrintMode BIT = 1




       DECLARE @BackupLocation VARCHAR(500)

       DECLARE @PurgeAferDays INT

       DECLARE @PurgingDate VARCHAR(30)


       DECLARE @FileName  VARCHAR(100)


       SET @PurgeAferDays = -14

       SET @BackupLocation = '\\central_storage_servername\BACKUPS\'+@@servername


       SET @PurgingDate = CONVERT(VARCHAR(19), DATEADD (dd,@PurgeAferDays,GETDATE()),126)


       SET @FileName = '?_full_'+

                     + REPLACE(CONVERT(VARCHAR(19), GETDATE(),126),':','-')



       SET @SQLCmd = '

              IF ''?'' <> ''tempdb'' BEGIN

                     EXECUTE master.dbo.xp_create_subdir N'''+@BackupLocation+'\?\'' ;


                     BACKUP DATABASE ? TO  DISK = N'''+@BackupLocation+'\?\'+@FileName+'''



                     EXECUTE master.dbo.xp_delete_file 0,N'''+@BackupLocation+'\?\'',N''bak'',N'''+@PurgingDate+''',1;



       IF @PrintMode = 1 BEGIN

              PRINT @SQLCmd



       EXEC sp_MSforeachdb @SQLCmd



Published Thursday, April 5, 2012 11:00 AM by Maria Zakourdaev

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Ian Yates said:

Great script.  We're an ISV and tend to have a mix of Express & Standard edition around the place at our various clients.  For those with standard edition I configure a maintenance plan but the customer's IT support company rarely knows more than how to spell SQL, and tend to like to see things in the Windows task scheduler.  I had a very simple version of the above script, but yours is is a bit more elegant.

What sort of approaches do you tend to recommend for monitoring of success/failure of backups?  Reindexing, etc?  I tend to just script things in SQLCMD and dump the output to a file, but that needs someone to know what a 100% success looks like vs a 90% success or complete failure.

April 6, 2012 5:11 AM

Jon Harn said:

Thanks for sharing your info. I really appreciate your efforts and I will be waiting for your further write ups thanks once again.

April 6, 2012 8:48 AM

Wiseman82 said:

I've never been a big fan of maintenance plans.  They have probably got better with newer versions of SQL server, but certainly in SQL 2005 they were buggy.  Also, in a dev environment you might have certain databases you want to exclude from backup but you might also want to ensure that any new databases created are included in the backup automatically.  Scripts work well and they offer additional flexibility.

Thanks for sharing your solution.  My own solution is posted here if it helps anyone:

Ideally you would receive an alert to inform you that the backup job has failed but I also like to check manually that the backup has completed without any issues.  I use this SP to have each server send me a status email on a daily basis:

It's also a good idea to test backups on a regular basis - particularly for production systems.

As Ian mentioned reindexing, here is my script based solution to re-indexing:

I find that it allows me to take a more targeted approach to index maintenance compared to index maintenance plans.  I use a SSRS report based on the two tables created in the script for monitoring (plus a failure notification on the agent job).

I'm work with a much smaller number of servers and the links I've posted are solutions I've developed based on my own specific requirements.  If they are useful to anyone else, great!

April 8, 2012 4:32 PM

Steve said:

I'd recommend looking at Ola Hallengren's solution which can be found here.

April 9, 2012 10:08 AM

Leave a Comment


About Maria Zakourdaev

The shortest word in the English language that contains the letters: abcdef is… feedback! Let me know if I have touched something that is alive in the cosmos.
Privacy Statement