purgeMSDB

IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.purgeMSDB') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.purgeMSDB
GO

CREATE PROC purgeMSDB 
   @source VARCHAR(30) = @@SERVERNAME,
   @sourcePassword VARCHAR(30) = ' ',
   @DeleteBeforeDate VARCHAR(30) = '1/1/1970',
   @debug VARCHAR(5) = 'false' 
   WITH RECOMPILE
   AS
  DECLARE @CMD VARCHAR(255),     -- reusable command holder 
          @CMDstatus INT,         -- return status of command prompt  
          @myName VARCHAR(30)
  
  SELECT @myName = 'purgeMSDB'

  SELECT @CMDstatus = 0
  IF (@DeleteBeforeDate = '1/1/1970')
     SELECT @DeleteBeforeDate = DATEADD(dd,-10,getdate())
  SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                ' -dmaster -Q"' + "sp_configure 'allow updates',1" + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
  SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                ' -dmaster -Q"reconfigure with override"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
  SELECT @CMD = 'DELETE msdb.dbo.sysbackupdetail WHERE backup_id IN ' +
                '(SELECT backup_id FROM msdb.dbo.sysbackuphistory ' + 
                "WHERE backup_start <= '" + @DeleteBeforeDate + "')"
  SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                ' -dmaster -Q"' + @CMD + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
  SELECT @CMD = "DELETE msdb.dbo.sysbackuphistory WHERE backup_start <= '" +
                @DeleteBeforeDate + "'"
  SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                ' -dmaster -Q"' + @CMD + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
  SELECT @CMD = 'DELETE msdb.dbo.sysrestoredetail WHERE restore_id IN ' +
                '(SELECT restore_id FROM msdb.dbo.sysrestorehistory ' + 
                "WHERE backup_start <=  '" + @DeleteBeforeDate + "')"
  SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                ' -dmaster -Q"' + @CMD + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
  SELECT @CMD = "DELETE msdb.dbo.sysRestorehistory WHERE backup_start <= '" +
                @DeleteBeforeDate + "'"
  SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                ' -dmaster -Q"' + @CMD + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
  SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                ' -dmaster -Q"' + "sp_configure 'allow updates',0" + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
  SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                ' -dmaster -Q"reconfigure with override"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
GO