In 2009, Adam Machanic (b|t) started the monthly blog event known as T-SQL Tuesday. One person picks a topic, and everyone is invited to blog about it on the second Tuesday of the month. The July 2018 host is Bert Wagner (b|t), and his chosen topic is: Code You Would Hate To Live Without. Talk about a broad topic! I could fill a book on that topic, and the same probably goes for many others.
But since my time is limited, I have decided to cherry-pick just two of my favorite scripts. This post is about the second: a small adaptation of Ola Hallengren’s fantastic database maintenance scripts.
Background
People who know me or follow my writings will know, or at least suspect, that database administration is not my primary interest. I see myself as a database developer, focused on writing T-SQL code to implement new solutions, debugging existing T-SQL code, and of course optimizing the performance of slow T-SQL code.
But several of my past and present customers are not large enough for separate DBA and DB-dev positions. So they ask me to do the DBA duties “on the side”. And since these are usually fairly simple infrastructures, with just a few servers, and no complex matters such as Always On Availability Groups, replication, or SAN administrators, I can handle those responsibilities.
One of my first actions for such clients is always to first review their maintenance setup. The second is usually to get rid of however they had it set up and replace it with something much better: Ola Hallengren’s database maintenance scripts.
BizTalk
That all went well until I got hired by a customer running BizTalk. The “problem” with BizTalk is that it comes with its own backup jobs, and not using those for backups invalidates support. So while I have my own personal opinions about these special BizTalk backup jobs, I can’t deny the relevant of support.
Unfortunately, the BizTalk backup job only runs backups for the databases that are part of the BizTalk setup. Other databases on the server are ignored; keeping them safe is suddenly on my again. Now if you add in that other people can create databases, either through BizTalk (that will then be maintained by BizTalk), or directly (and not included in the BizTalk backup logic), you may start to see how it can be … challenging to make sure that I have backups for exactly all the right databases on the server.
Enhancing Ola
Eventually I found a good solution to this problem. A few lines of code to enhance Ola (or at least his backup script). Here is a sample of the code that I now use for the backup jobs on all BizTalk instances (this one is for the full backup of all databases; I am sure you can work out the changes in the jobs for differential and log backups):
SET QUOTED_IDENTIFIER ON;
-- Ensure that databases under BizTalk control are not backed up by this job
DECLARE @db nvarchar(MAX) = N'ALL_DATABASES';
SET @db += (SELECT N',-' + DatabaseName
FROM BizTalkMgmtDb.dbo.admv_BackupDatabases
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)');
-- Back up
EXECUTE dbo.DatabaseBackup @Databases = @db,
@Directory = N'\\CIT-ARCHIVE01\SQLBACKUPS3',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 340,
@CheckSum = 'Y',
@LogToTable = 'Y';
What this script does is:
- Check the table that the BizTalk backup job uses to find which database it needs to process.
- Create a string variable that reads “ALL_DATABASES,-db1,-db2,…” (using all those BizTalk managed databases in the concatenation).
- Pass that string to Ola’s backup script, which tells it to run a backup for all databases except those in the enumeration.
This works perfect for me. When a new BizTalk database is created, BizTalk itself adds it to the admv_BackupDatabases table and Ola’s job will never touch it. When a new non-BizTalk database is created, Ola’s own logic will pick it up for processing. And I can sit back and relax, knowing that each database on this server is safely backed up by exactly one of the two backup procedures.
