Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Validate SQL Server Backups Exist with Policy Based Management


By:   |   Last Updated: 2009-08-13   |   Comments (2)   |   Related Tips: More > 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


Last Updated: 2009-08-13


next webcast button


next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

While @@FetchStatus = 0

Begin

End

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

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 sd.name NOT IN ('Pubs','tempdb','Northwind', 'Adventureworks');


Learn more about SQL Server tools