Delete BizTalk backup files

by community-syndication | Sep 5, 2012 | BizTalk Community Blogs via Syndication

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

Developing BizTalk 2010 R2 with Visual Studio 2011??

by community-syndication | Sep 4, 2012 | BizTalk Community Blogs via Syndication

Since a couple of days Microsoft enabled Windows Azure users to create Virtual Machines, which are based on BizTalk 2010 R2 CTP. This is great, because it offers those users to have a preview at that release, combined with some of the new related products, like SQL Server 2012 and .NET 4.5.

However, given the screenshot below, which was taken from the BizTalk 2010 R2 Installation Wizard, it looks like Microsoft expected to release the next version of Visual Studio sooner 🙂

The document type does not match any of the given schemas – Encountered in an XML Disassembler/Assembler based scatter-gather scenario

by community-syndication | Sep 2, 2012 | BizTalk Community Blogs via Syndication

I’ve been working on a BizTalk solution which includes a scenario in which I am receiving envelope messages that are to be debatched in a receive pipeline using an XML Disassembler pipeline component, correlating the debatched messages which will be of different message types to relevant orchestrations where they will once again be batched up […]
Blog Post by: Johann

Unable to assign context properties to a BizTalk message until all body parts have been instantiated- use of unconstructed message

by community-syndication | Sep 1, 2012 | BizTalk Community Blogs via Syndication

Some colleagues of mine called me over for some advice on a problem they were having in a BizTalk orchestration the other day. They were using a message assignment shape to set the bodies and context properties for a multi-part message which contained two parts which were to be sent out over SMTP on a […]
Blog Post by: Johann

When failure occurs!

by community-syndication | Aug 31, 2012 | BizTalk Community Blogs via Syndication

So over the years I’ve had my share of “meltdowns” or failures in BizTalk. I pretty much feel like I’ve seen them all, and I would like to contribute some of the errors and solution to this problems, call it a teaser since there is a lot, this will be the first post in a
Blog Post by: Tord Glad Nordahl

BizTalk 2010 R2 CTP and Preview VM’s in Azure

by community-syndication | Aug 31, 2012 | BizTalk Community Blogs via Syndication

I have just been looking at http://blogs.msdn.com/b/biztalk_server_team_blog/archive/2012/08/29/getting-started-with-biztalk-server-2010-r2-ctp-in-windows-azure-virtual-machines.aspx to understand some of the new features. I am not going to wax on about any of the new features here because other people are already doing a good job at this (See  http://blogs.msdn.com/b/biztalk_server_team_blog/archive/2011/12/08/biztalk-server-2010-r2.aspx) but I want to highlight how refreshing it is to preview a new product […]
Blog Post by: mbrimble

Using System Colors in C#

by community-syndication | Aug 31, 2012 | BizTalk Community Blogs via Syndication

When choosing a color in the properties window in visual studio, there is the possibility to choose from so called System Colors. An example for this is the color ‘Window’, which is the default for a textbox.
Now, I tried to set the BackColor of a Textbox programmatically back to this system color ‘Window’. Turned out not to be as easy as I thought it would be.

I tried things like Color.? … I hoped to find the color in this namespace, or even a System subsection for the systemcolors. Nothing to be found however.
Nor was there any namespace like System.Color to be found.

But how can these system colors be set from C# code than?

Well, one way turned to be using this command:

System.Drawing.Color.FromKnownColor(KnownColor.Window)

And, as you’d expect, the KnownColor enumeration contains all of the other user-definable colors.

Or even an easier method to achieve this is

System.Drawing.SystemColors.Window