How to create a Maintenance Plan to delete BizTalk Database Backups files

BizTalk Server database databases and their health are very important for a successful BizTalk Server database messaging environment. This is nothing new, and everybody knows!

Although there can be many settings that we can configure, like auto-growth settings for BizTalk Databases (you can learn more here), there are two main things that we must understand and be aware of, especially the database administrators:

• The execution of the BizTalk Server SQL Agent jobs is crucial for managing the BizTalk Server databases and for maintaining optimal performance.
• The Backup BizTalk Server job is the only supported method to back up the BizTalk Server databases and requires that all of the BizTalk Server databases be configured to use the SQL Server full recovery model.

BizTalk Server 2010/2013 is shipped out with a total of 13 SQL Agent jobs. 2 of these jobs must be configured. The two jobs that need configuration are the two most important jobs:

• The Backup BizTalk Server: This is the job provided by Microsoft to do a best practice backup of the BizTalk databases.
• And the DTA Purge and Archive: This SQL Agent job purges and archives information from the tracking database. You do need to configure this job in order for it to work.

However, what we often forget is that these two jobs, by default, don’t provide functionalities for deleting backup files that have accumulated over time on our file system, and we normally forget to create a “process” or a “job” to accomplish this until it’s too late. The result of that is… lots of times we just remember when disks are full, and everything stops working!

We can, for example, implement your custom sp_ DeleteBackupHistoryAndFiles as you can see in my post: BizTalk 2013 Installation and Configuration – Configure BizTalk Server SQL Jobs (Part 15), however, I personally don’t like this approach for two reasons:

• I don’t like to change the scripts of the standard BizTalk jobs.
• And I also believe that this approach is very limited and doesn’t allow the flexibility that we all want.

Instead, I prefer to create an SQL Server Maintenance Plan to delete BizTalk Database Backups to accomplish this task.

Maintenance Plan to clean BizTalk Database backup files

Maintenance Plans allow DBAs to have the flexibility to create a workflow to execute several tasks required to make sure that databases are optimized, regularly backed up, and free of inconsistencies. However, almost all of these tasks are warranted by the existing BizTalk jobs, and I will not use them. The only thing we need is to create a task to clean the BizTalk Database backup files from our file system.

The main advantage of this approach is that it will allow us more flexibility for further changes, and we can also use it to other backups that we have.

How to create a Maintenance Plan to clean BizTalk Database backup files

As a personal note, you should ensure that the SQL Server Agent service is running because the maintenance plans depend on the Microsoft SQL Server Agent in order to run on a regular basis.

Important Note: This example is created to run on a developer machine, so if you are implementing this approach in production environments, make sure to keep the backups for the time you see that fits your requirements, or make sure the backups are saved in external storage before you delete these files from the file system.

To create the maintenance plan, you need to:

• Open the SQL Server Management Studio by choosing Start > Programs > Microsoft SQL Server [edition] > SQL Server Management Studio.

Or

• Press the Windows key to switch to Windows Server 2012 Start Screen and type SQL Management or SQL, and click on the SQL Server Management Studio option on the Apps menu.
• Expand the server and then the Management folder.
• Right-click Maintenance Plans and select Maintenance Plan Wizard. This launches the wizard, and you can now step through and create a plan customized to meet your maintenance requirements.

• On the SQL Server Maintenance Plan Wizard page, click Next.

• On the Select Plan Properties page:
  • In the Name: enter the name of the maintenance plan you are creating.
  • In the Description: enter a brief description of your maintenance plan.
  • In the Run as: specify the credential that Microsoft SQL Server Agent uses when executing the maintenance plan – leave the default.
  • Select the Single schedule for the entire plan or no schedule option to specify the recurring schedule of the maintenance plan.

• Under Schedule, click Change…
  • Under Frequency, on the Occurs list, select Daily and in the Recurs every box, enter how often the job schedule repeats in days: 1 Day.
  • Under Daily frequency, select Occurs once at and specify the time of day when the job schedule should run: 12:00:00.
  • Leave the default values for the rest of the properties and click OK.

• Back to the Select Plan Properties page, click Next.
• On the Select Maintenance Tasks page, select Maintenance Cleanup Task from the list and click Next.

• On the Select Maintenance Task Order page, click Next to continue
• On the Define Maintenance Cleanup Task page, specify the following properties:
  • Under Delete files of the following type: select Backup files.
  • Select Search folder and delete files based on an extension to delete all files with the specified extension in the specified folder:
    • Under Folder: specify the path and name of the folder containing the files to be deleted.
    • Under File extension: Provide the file extension of the files to be deleted.
    • Select also the Include first-level subfolders option if to want to delete the files also from first-level subfolders under the folder specified in Folder.
  • Select Delete files based on the age of the file at task run time and specify the minimum age of the files that you want to delete under the Delete files older than the following property:
    • Specify 1 Day.
  • Click Next.

• On the Select Report Options page, click Next.
• On the Complete the Wizard page, verify the choices made in the previous pages and click Finish.

• On the Maintenance Wizard Progress page, verify if every action were successful executed and then click Close.

Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son.