Tuesday, December 2, 2008

Find databases missing a backup

If you manage multiple servers, and you don't have complete control over your database backups, then you need to be checking regularly that all of your databases are indeed being backed up. With SQL Server 2008, Policy Based Management makes this very easy to do. There is a Microsoft Books Online entry for this:

Monitoring and Enforcing Best Practices by Using Policy-Based Management

You don't have to be on a SQL Server 2008 server to use PBM. You can actually use it manually from SQL Server Management Studio by downloading the Express Edition of 2008 with Tools. However, if you are not taking the leap to SQL Server 2008 anytime soon, you still need to know how to monitor this. You can do so with a simple TSQL Query using a few system tables:


SELECT database_name, last_backup
    
FROM
    
(  
    
SELECT database_name, MAX(backup_finish_date) [last_backup]
        
FROM msdb.dbo.backupset
                
JOIN MASTER..sysdatabases d ON database_name = d.name
        
WHERE TYPE = 'd'
        
GROUP BY database_name
        
UNION ALL
    
SELECT d.name, NULL
        
FROM MASTER..sysdatabases d
        
WHERE NOT EXISTS (
        
SELECT 1
            
FROM msdb..backupset
            
WHERE d.name = database_name)
    )
AS tab
    
WHERE tab.last_backup < GETDATE()-14
        
OR tab.last_backup IS NULL

I intentionally use the legacy table sysdatabases table so that this script works across all platforms exactly the same way. If you were to download the Express Edition of SQL Server 2008 Management Studio, you could hit all of your SQL Servers regardless of edition, (2000, 2005 and 2008) with this on a multi-server query. Want to know how to do that, see my article on this
Multiple Server Queries with SSMS 2008 - SQL Server Central

I know that is kind of a shameless plug, but it is what it is.

3 comments:

  1. Good day sir :)

    http://extras.sqlservercentral.com/prettifier/prettifier.aspx

    Great way to include source code in your blog posts. (The reason I say this is because when you currently cut and paste your code into mgmt studio you lose all formatting and spacing is kinda pooched too)

    Now here's my shameless plug
    http://benchmarkitconsulting.com/

    Have a good one! :)

    ReplyDelete
  2. I use LiveWriter for my blogging, and the formatting I see in my blog is exactly what I would expect to see based on how I am writing things. I can also copy and paste the code from FireFox correctly, but not from IE6. It is a StyleSheet interpretation issue, and try as I might, I can't please every browser every time.

    I'll look at my Analytics reports and see which browsers are most common and fix the style sheet to be accurate for the most people involved.

    ReplyDelete
  3. hehehe wow... that's alot more work then what I would do (kudos to you for being so dedicated).

    Hope you didn't take it as a critique... I was just sharing the tool I use that seems to work no matter what browser people are using.

    Happy Blogging! :)

    ReplyDelete