woensdag 5 september 2012

Delete BizTalk backup files

The SQL Server agent job "Backup BizTalk Server" will not delete the generated backup files automatically. The job does clear the backup history table in the database, but it will never delete the backup files from the disk. Which will of course result in the disk to fill up eventually and the backup job will fail from then on.

Here's a simple stored procedure to call from the "Backup BizTalk Server" job in the "Clear Backup History" step. Just follow these steps:
  1. Open SQL Server management Studio
  2. Open a new query window and connect to the BizTalkMgmtDb database
  3. Execute this script to add a new stored procedure called sp_DeleteBackupHistoryAndFiles
    USE [BizTalkMgmtDb]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null, @UseLocalTime bit = 0
    
    AS
     BEGIN
    
     set nocount on
    
      IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
      RETURN
    
         /*
            Only delete full sets
            If a set spans a day such that some items fall into the deleted group and the other don't don't delete the set
    
            Delete history only if history of full Backup exists at a later point of time
            why: history of full backup is used in sp_BackupAllFull_Schedule to check if full backup of databases is required or not.
            If history of full backup is not present, job will take a full backup irrespective of other options (frequency, Backup hour)
        
        */
            
      declare @PurgeDateTime datetime
    
      if (@UseLocalTime = 0)
        set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETUTCDATE())
      else
        set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETDATE())
    
      DECLARE DeleteBackupFiles CURSOR
    
      FOR SELECT 'del "' + [BackupFileLocation] +  case right(BackupFileLocation,1) when '\' then '' else '\' end + [BackupFileName] + '"' FROM [adm_BackupHistory] [h1]
        WHERE     [BackupDateTime] < @PurgeDateTime
        AND    [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [h1].[BackupSetId] AND [h2].[BackupDateTime] >= @PurgeDateTime)
        AND EXISTS( SELECT TOP 1 1 FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] > [h1].[BackupSetId] AND [h2].[BackupType] = 'db')
    
      DECLARE @cmd varchar(400)
    
      OPEN DeleteBackupFiles
    
       FETCH NEXT FROM DeleteBackupFiles INTO @cmd
    
      WHILE (@@fetch_status <> -1)
      BEGIN
                IF (@@fetch_status <> -2)
                BEGIN
    
                            EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
    
                            delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
    
                            print @cmd
                END
    
                FETCH NEXT FROM DeleteBackupFiles INTO @cmd
      END
    
      CLOSE DeleteBackupFiles
    
      DEALLOCATE DeleteBackupFiles
    
      END
    
  4. 5.Modify the "Clear Backup History" step of the Backup BizTalk Server job to call sp_DeleteBackupHistoryAndFiles, instead of calling the stored procedure sp_DeleteBackupHistory
  5. Make sure xp_cmdshell for the SQL Server instance is enabled. this will be disabled by default. To enable this, execute following SQL script:
    EXEC master.dbo.sp_configure 'show advanced options', 1
    
    RECONFIGURE
    
    EXEC master.dbo.sp_configure 'xp_cmdshell', 1
    
    RECONFIGURE
    
    


This stored procedure is made for BizTalk Server 2010.
Because with the release of BizTalk 2010 there have been changes to the sp_DeleteBackupHistory stored procedures. They added a parameter to the stored procedure to use local time and changed the query to prevent the deletion of the history from the last full backup set forward.

For older versions of BizTalk you should use this script instead:
CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null
AS
BEGIN
set nocount on
  IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
  RETURN
/*
  Only delete full sets
  If a set spans a day such that some items fall into the deleted group and the other doesn't, do not delete the set
*/ 

DECLARE DeleteBackupFiles CURSOR
FOR SELECT 'del "' + [BackupFileLocation] + '\' + [BackupFileName] + '"' FROM [adm_BackupHistory]
WHERE  datediff( dd, [BackupDateTime], getdate() ) >= @DaysToKeep
AND [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [BackupSetId] AND datediff( dd, [h2].[BackupDateTime], getdate() ) < @DaysToKeep )
DECLARE @cmd varchar(400)
OPEN DeleteBackupFiles
FETCH NEXT FROM DeleteBackupFiles INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN 

            IF (@@fetch_status <> -2)
            BEGIN
                        EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
                        delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
                        print @cmd
            END 

            FETCH NEXT FROM DeleteBackupFiles INTO @cmd
END 

CLOSE DeleteBackupFiles
DEALLOCATE DeleteBackupFiles
  END
GO



Source: http://www.biztalkbill.com/Home/tabid/40/EntryId/81/Update-to-Stored-Procedure-to-delete-Backup-BizTalk-Server-SQL-Agent-backup-files.aspx

Geen opmerkingen:

Een reactie posten