Validate SQL Server Backups Exist with 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('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.
- 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
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips