Validate SQL Server Backups Exist with Policy Based Management

By:   |   Comments (2)   |   Related: > Policy Based Management


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.


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', '
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
 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 NOT IN (''Pubs'',''tempdb'',''Northwind'', ''Adventureworks'')
 OPEN FileNameCsr
 WHILE (@@fetch_status <> -1)
  IF (@@fetch_status <> -2)
   PRINT @FileName
   EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

CLOSE FileNameCsr
--what we want to return
SELECT @File_Exists

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:

PBM fileexist new condition

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:

PBM fileexist new policy

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, item number 481566)

PBM fileexist evaluate policy

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Thomas LaRock 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

Comments For This Article

Wednesday, August 26, 2009 - 12:41:33 PM - ray.herring Back To Top (3964)

In your cursor while loop why don't you simplify the logic.

While @@FetchStatus = 0



I don't see any reason to explicitly test for -1 and -2 unless you expect to do things with the cursor record set.

Tuesday, August 25, 2009 - 10:34:28 AM - darkrum Back To Top (3955)

The query to get physical_device_name needs to be modified with underlined data to to get a single row for DB_NAME().  Please excuse me if I am missing something in logic.


SELECT physical_device_name

FROM msdb.dbo.backupmediafamily bmf JOIN msdb.dbo.backupset bms ON

(bmf.media_set_id = bms.media_set_id)

JOIN master.sys.sysdatabases sd ON

( bms.database_name = db_name() and sd.[name] = db_name()  AND bms.backup_start_date =

(SELECT max(backup_start_date) FROM [msdb].dbo.[backupset] b2

WHERE bms.database_name = b2.database_name and b2.type = 'D'))

WHERE NOT IN ('Pubs','tempdb','Northwind', 'Adventureworks');

get free sql tips
agree to terms