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

AS

BEGIN

       DECLARE @BackupLocation VARCHAR(500)

       DECLARE @PurgeAferDays INT

       DECLARE @PurgingDate VARCHAR(30)

       DECLARE @SQLCmd  VARCHAR(MAX)

       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),':','-')

                     +'.bak';

       SET @SQLCmd = '

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

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

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

                     WITH NOFORMAT, NOINIT,  SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10 ;

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

              END'

       IF @PrintMode = 1 BEGIN

              PRINT @SQLCmd

       END

       EXEC sp_MSforeachdb @SQLCmd

END