Validate SQL Server Backups Exist with 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.