Validate SQL Server Backups Exist with Policy Based Management

By: Thomas LaRock   |   Updated: 2009-08-13   |   Comments (2)   |   Related: > Policy Based Management

Problem

I recently submitted a tip regarding the existence of your backup files using the undocumented system stored procedure xp_fileexist. But how would you go about executing that script against all your SQL Server 2005 and SQL Server 2008 instances? I took that script and decided to go one step further using Policy Based Management to create a custom policy that you can then run against all your SQL Server 2005 and SQL Server 2008 instances.

Solution

Policy Based Management allows for the creation of custom policies based upon the execution of custom T-SQL code. The key lies in the creation of a custom policy condition using the ExecuteSQL function. By using this function and applying logic that results in a yes/no or on/off result set you can create any custom policy imaginable.

The policy will run against every database on your SQL Server 2005 and SQL Server 2008 instances. It will use xp_fileexist to determine if the database backup file still exists in the file location where it was originally written. If it does not then it will return an error.

ExecuteSql function

ExecuteSql('Numeric', '
SET NOCOUNT ON
DECLARE @FileName varchar(255)
DECLARE @File_Exists int
DECLARE @dbname sysname
SET @File_Exists = 0 -- initialize to failure in order to capture dbs that 
   -- have never had a backup taken
SELECT @dbname = DB_NAME()
--get list of files to check
DECLARE FileNameCsr CURSOR
READ_ONLY
FOR 
 SELECT physical_device_name
 FROM msdb..backupmediafamily bmf
 INNER JOIN msdb..backupset bms ON bmf.media_set_id = bms.media_set_id
 INNER JOIN master..sysdatabases sd ON bms.database_name = DB_NAME()
 AND bms.backup_start_date = (SELECT max(backup_start_date) FROM [msdb]..[backupset] b2
    WHERE bms.database_name = b2.database_name and b2.type = ''D'')
 WHERE sd.name NOT IN (''Pubs'',''tempdb'',''Northwind'', ''Adventureworks'')
BEGIN TRY
 OPEN FileNameCsr
 FETCH NEXT FROM FileNameCsr INTO @FileName
 WHILE (@@fetch_status <> -1)
 BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
   PRINT @FileName
   EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT
  END
 
 FETCH NEXT FROM FileNameCsr INTO @FileName
 END
 
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

CLOSE FileNameCsr
DEALLOCATE FileNameCsr
--what we want to return
SELECT @File_Exists
GO
')

Setup the Condition and Test the Policy

Connect to an instance of SQL Server 2008 and navigate to the 'Policies' folder. Right click and select 'new policy'. In the name field you can enter in 'Database Backup File Check Policy', then click on the arrow in the Check condition dropdown and select 'New condition'. In the name field here enter 'Database Backup File Check Condition'. For the facet select 'Database', then click on the ellipses next to the 'Field' dropdown. Here is where the magic will happen, take the code above and cut and paste into the area labeled 'cell value'. When you are done you will click 'OK', then set the value field equal to 1. You should see a screen similar to the following:

Click 'OK' and you should come back to the original screen for creating a new policy. Make certain the checkbox is enabled as shown below:

Your policy is now created, the only thing left is to evaluate it against any or all instances. The easiest method is to right-click on the policy itself and select 'evaluate'. And yes, that is blue on blue and it is near impossible to read. I have no idea how something so abhorrent could get past quality control at Microsoft, but it did. (I opened a connect item on this, you can vote on it at http://connect.microsoft.com, item number 481566)

Click 'Evaluate' then 'Run' if you get prompted for the third time about your policy containing a script. Since xp_fileexist returns a 1 if the file is found, then the policy will flag something as a failure if any value other than a 1 is returned. For every database that has a current database backup file that exists on disk the policy will return a success.

Next Steps

• Create the policy as demonstrated above and run against your SQL 2005 and SQL 2008 instances.
• Check out this tip on Policy Based Management.
• Check out the recent SQL Server 2008 tips.

About the author

Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips