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

 

Identify SQL Server Databases Not Recently Backed Up Using Policy Based Management


By:   |   Last Updated: 2010-09-22   |   Comments (5)   |   Related Tips: More > Policy Based Management

Problem

As the DBA, you need to determine if there are any databases in your production environment which have not had a full backup in the last 24 hours. In this tip we cover the step by step process for setting up a policy to identify all the databases which are not backed up in the last 24 hours.

Solution

Database Administrators can use Policy Based Management (PBM) to identify databases which have not backed up in the last 24 hours. You can follow the below steps to create and evaluate the policy. It's assumed that the DBA is taking full backups of all the user databases once a day. However, there are certain exceptions when Full Backups for a database are performed once in a week especially for very large databases, but for this example we are expecting all databases to be backed up daily.

If you are not familiar with Policy Based Management (PBM) then you can start with the following tip: Using Policy-Based Management in SQL Server 2008.


1. Connect to SQL Server 2008 instance using SQL Server 2008 Management Studio and expand Management -> Policy Management and then right click Policies and select New Policy... as shown in the snippet below.

 Connect to SQL Server 2008 instance using SQL Server 2008 Management Studio

2. In the Create New Policy window provide the name of the policy as "Identify Databases Not Backed up in the Last 24 hours". In Check condition you need to click the New condition.... option to open up the Create New Condition window.

Identify Databases Not Backed up in the Last 24 hours

3. In the Create New Condition window you need to provide the name of the condition as "Databases Not Backed up in the Last 24 hours" and then select the Facet "Database". Under Expression select Field value as @LastBackupDate and choose operator value as ' >= ' and then the value as DateAdd('HH', -25, GetDate()) as shown in the below snippet. Click OK to save the condition and to return to the parent Create New Policy window. This will check for any backup 25 hours older than now.

  • Use @LastBackupDate field to identify the date and time when the database was last backed up
  • Use @LastDifferentialBackupDate to identify the data and time when the last differential backup was performed
  • Use @LastLogBackupDate to identify the data and time when the transaction log was last backed up

The value as DateAdd(HH, -25, GetDate())

This will check for any backup 25 hours older than now.

4. In Create New Policy, by default Every Database option under Against targets will be checked as shown in the below snippet. Click OK to create the policy.

By default every Database option under Against targets will be checked

5. Once your policy is created, the next step will be to evaluate the policy. Right click the policy and select the Evaluate option from the drop down list as shown in the below snippet.

click the policy and select the Evaluate option

6. The below snippet shows the result set returned by the policy. We can see that for one of the database AdventureWorks the policy has failed. The reason for the failure is AdventureWorks database was not backed up in the last 24 hours.

The below snippet shows the result set returned by the policy

7. When you click a "View..." link under Details, you will be able to see a detailed result for the AdventureWorks database. The expected value is "Current Date - 25 hours", however the actual value reflects the time the last full backup was done for the AdventureWorks database.

When you click a View link under Details, you will be able to see a detailed result

After you find the databases out of compliance, the next step would be to perform full backups of these databases.

Next Steps


Last Updated: 2010-09-22


next webcast button


next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

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.



    



Friday, March 29, 2013 - 6:12:41 AM - Ashish Back To Top

Check out the following tip Configuring Alerts for SQL Server Policy Based Management which answers your question.


Thursday, March 28, 2013 - 12:44:21 AM - sanjeev Back To Top

Thanks for the Nice post.... But now if we evalute the policy on schedule basis and if we get failed basis, we need to get notified vis mail, how can we do this?


Thursday, August 18, 2011 - 8:51:53 AM - gunjan Back To Top

Thanks for posting such a nice blog. It gave me the quick thing which I was looking for to resolve on the issues. Hope to see some more good blogs from you.


Friday, October 15, 2010 - 12:12:54 PM - CJ Morgan Back To Top
Oh, nevermind...figured it out .  It's been a long week. :-)


Friday, October 15, 2010 - 12:09:17 PM - CJ Morgan Back To Top
Not sure if it's a limitation of 2008 R2 but when I try to put the statement "DateAdd('HH', -25, GetDate())" in the value field,I'm unable to, only can choose a date. 
"



Learn more about SQL Server tools