Maintenance plans love story
- by Maria Zakourdaev
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
