Database Backup Snapshot

 

Every Monday morning I always make it a point to run some basic checks on our production database servers.  Yes, there are alerts… but sometimes those alerts get over looked, don’t get delivered, etc.  I like to know implicitly that we are in decent shape…

In previous posts I showed how to look at the server at the instance level:

http://www.daveturpin.com/2011/04/dba-query-instance-information/

And at the database level:

http://www.daveturpin.com/2011/07/list-of-databases/

Today I will show how I take a look at the database backups… probably THE MOST IMPORTANT service a DBA provides to the organization.  Without good backups a loss of data is a real possibility… which would probably be followed up with a loss of a job…

Reviewing database backups can by tricky because we have 3 possible backup varieties to consider: FULL, DIFFERENTIAL and TRANSACTION LOGS.  FULL backups apply to all databases.  DIFFERENTIAL definitely do not apply to the system databases and may apply to user databases.  TRANSACTION log backups do not apply to databases in SIMPLE recovery mode.

To tame this beast, I wrote a script the includes the recovery model, the number of days since the last FULL, DIFF and TRN backups.  If the database is SIMPLE, the TRN backup info will be minus one (-1).

So here’s the query:

Last Backups
— Get the last FULL backup
— drop table #full
SELECT    d.database_id
        , d.recovery_model AS recovery_model_id
        , bus.backup_set_id
        , d.Name as DatabaseName
        , 'FULL' AS [Backup Type]
        , Convert(varchar(12), bus.backup_finish_date, 101)    AS LastBackUpTaken
        , CAST(NULL AS numeric(8, 2)) AS [Backup Time(min)]
        , Convert(varchar(12), bus.[user_name], 101)    AS UserName
INTO #FULL
FROM        sys.databases d
        INNER JOIN msdb.dbo.backupset bus
            ON    bus.database_name = d.name
            AND    bus.[type] = 'D'
WHERE    bus.backup_set_id IN (    SELECT    TOP 1
                                        bus2.backup_set_id
                                FROM        msdb.dbo.backupset bus2
                                WHERE    bus2.database_name = d.name
                                AND        bus2.[type] = 'D'
                                ORDER BY bus2.backup_set_id DESC)
UNION

— Identify any dbs that never had a backup taken.
SELECT    d.database_id
        , d.recovery_model AS recovery_model_id
        , bus.backup_set_id
        , d.Name as DatabaseName
        , 'FULL' AS [Backup Type]
        , 'No FULL Backups'
        , CAST(NULL AS numeric(8, 2)) AS [Backup Time(min)]
        , Convert(varchar(12), bus.[user_name], 101)    AS UserName
FROM        sys.databases d
        LEFT OUTER JOIN msdb.dbo.backupset bus
            ON    bus.database_name = d.name
            AND    bus.[type] = 'D'
WHERE    bus.database_name IS NULL;

—- Differential Backups —-
— Get the last FULL backup
— drop table #DIFF
SELECT    d.database_id
        , d.recovery_model AS recovery_model_id
        , bus.backup_set_id
        , d.Name as DatabaseName
        , 'DIFF' AS [Backup Type]
        , Convert(varchar(12), bus.backup_finish_date, 101)    AS LastBackUpTaken
        , CAST(NULL AS numeric(8, 2)) AS [Backup Time(min)]
        , Convert(varchar(12), bus.[user_name], 101)    AS UserName
INTO #DIFF
FROM        sys.databases d
        INNER JOIN msdb.dbo.backupset bus
            ON bus.database_name = d.name
WHERE    bus.[type] = 'I'
AND        bus.backup_set_id IN (    SELECT    TOP 1
                                        bus2.backup_set_id
                                FROM        msdb.dbo.backupset bus2
                                WHERE    bus2.database_name = d.name
                                AND        bus2.[type] = 'I'
                                ORDER BY bus2.backup_set_id DESC)
                                
UNION

— Identify any dbs that never had a backup taken.
SELECT    d.database_id
        , d.recovery_model AS recovery_model_id
        , bus.backup_set_id
        , d.Name as DatabaseName
        , 'DIFF' AS [Backup Type]
        , 'No DIFF Backups'
        , CAST(NULL AS numeric(8, 2)) AS [Backup Time(min)]
        , Convert(varchar(12), bus.[user_name], 101)    AS UserName
FROM        sys.databases d
        LEFT OUTER JOIN msdb.dbo.backupset bus
            ON    bus.database_name = d.name
            AND    bus.[type] = 'I'
WHERE    bus.database_name IS NULL;

—- Log Backups —-
— drop table #LOG
SELECT    d.database_id
        , d.recovery_model AS recovery_model_id
        , bus.backup_set_id
        , d.Name as DatabaseName
        , 'LOG' AS [Backup Type]
        , Convert(varchar(12), bus.backup_finish_date, 101)    AS LastBackUpTaken
        , CAST(NULL AS numeric(8, 2)) AS [Backup Time(min)]
        , Convert(varchar(12), bus.[user_name], 101)    AS UserName
INTO #LOG
FROM        sys.databases d
        INNER JOIN msdb.dbo.backupset bus
            ON bus.database_name = d.name
WHERE    bus.[type] = 'L'
AND        d.recovery_model = 1
AND        bus.backup_set_id IN (    SELECT    TOP 1
                                        bus2.backup_set_id
                                FROM        msdb.dbo.backupset bus2
                                WHERE    bus2.database_name = d.name
                                AND        bus2.[type] = 'L'
                                ORDER BY bus2.backup_set_id DESC)
                                
UNION

— Identify any dbs that never had a backup taken.
SELECT    d.database_id
        , d.recovery_model AS recovery_model_id
        , bus.backup_set_id
        , d.Name as DatabaseName
        , 'LOG' AS [Backup Type]
        , 'No LOG Backups'
        , CAST(NULL AS numeric(8, 2)) AS [Backup Time(min)]
        , Convert(varchar(12), bus.[user_name], 101)    AS UserName
FROM        sys.databases d
        LEFT OUTER JOIN msdb.dbo.backupset bus
            ON    bus.database_name = d.name
            AND    bus.[type] = 'L'
WHERE    bus.database_name IS NULL
AND        d.recovery_model = 1;

— Get the backup run time:    
UPDATE    t
SET        [Backup Time(min)] = (DATEDIFF(ms, bus.backup_start_date, bus.backup_finish_date) * 1.0) / (60 * 1000)
FROM        #FULL t
        INNER JOIN msdb.dbo.backupset bus
            ON bus.backup_set_id = t.backup_set_id;
            
UPDATE    t
SET        [Backup Time(min)] = (DATEDIFF(ms, bus.backup_start_date, bus.backup_finish_date) * 1.0) / (60 * 1000)
FROM        #DIFF t
        INNER JOIN msdb.dbo.backupset bus
            ON bus.backup_set_id = t.backup_set_id;
            
UPDATE    t
SET        [Backup Time(min)] = (DATEDIFF(ms, bus.backup_start_date, bus.backup_finish_date) * 1.0) / (60 * 1000)
FROM        #LOG t
        INNER JOIN msdb.dbo.backupset bus
            ON bus.backup_set_id = t.backup_set_id;

— Pull it all together:
SELECT    CONVERT(int, CONVERT(char, current_timestamp, 112)) AS CaptureDate
        , f.database_id
        , f.DatabaseName
        , CASE f.recovery_model_id
            WHEN 1 THEN 'FULL'
            WHEN 2 THEN 'BULK'
            WHEN 3 THEN 'SIMPLE'
            ELSE 'Unknown'
          END AS [Recovery_Model]
         , f.backup_set_id AS [FULL_Backup_Set_ID]
         , f.LastBackUpTaken AS [Last_FULL_Backup_Date]
         , DATEDIFF(DAY,
                    CAST(    CASE f.LastBackUpTaken
                                WHEN 'No FULL Backups' THEN DATEADD(DAY, 1, CURRENT_TIMESTAMP)
                                ELSE f.LastBackUpTaken
                            END AS SMALLDATETIME),
                    CURRENT_TIMESTAMP) AS [#Days Since Last FULL]
         , f.[Backup Time(min)] AS [FULL_Backup_Time_min]
         , f.UserName AS [FULL_Backup_Operator]
         
         , d.backup_set_id AS [DIFF_Backup_Set_ID]
         , d.LastBackUpTaken AS [Last_DIFF_Backup_Date]
         ,  DATEDIFF(DAY,
                    CAST(    CASE d.LastBackUpTaken
                                WHEN 'No DIFF Backups' THEN DATEADD(DAY, 1, CURRENT_TIMESTAMP)
                                ELSE d.LastBackUpTaken
                            END AS SMALLDATETIME),
                    CURRENT_TIMESTAMP) AS [#Days Since Last DIFF]
         , d.[Backup Time(min)] AS [DIFF_Backup_Time_min]
         , d.UserName AS [DIFF_Backup_Operator]
         
         , l.backup_set_id AS [LOG_Backup_Set_ID]
         , l.LastBackUpTaken AS [Last_LOG_Backup_Date]
         ,  DATEDIFF(DAY,
                    CAST(    CASE l.LastBackUpTaken
                                WHEN 'No LOG Backups' THEN DATEADD(DAY, 1, CURRENT_TIMESTAMP)
                                ELSE l.LastBackUpTaken
                            END AS SMALLDATETIME),
                    CURRENT_TIMESTAMP) AS [#Days Since Last LOG]
         , l.[Backup Time(min)] AS [LOG_Backup_Time_min]
         , l.UserName AS [LOG_Backup_Operator]
FROM        #FULL f
        LEFT OUTER JOIN #DIFF d ON d.database_id = f.database_id
        LEFT OUTER JOIN #LOG l ON l.database_id = f.database_id
— Exclude tempdb
WHERE    f.database_id <> 2
ORDER BY f.database_id;

 

OK, it’s a little long… but worth the effort.  If you manage more than one database server, which most DBAs do, you can plug this query into a Central Management Server query window and get a snapshot of all of your database backups in one easy view.

I like to put the result set into an excel file and filter on the Recovery model to make sure my FULL recovery databases are running transaction log backups.  How many times have you answered the call to a developer or end user complaining about a full transaction log.  I’m happy to say that because of this simple review process, I never get the call for PRODUCTION trans log backup issues due to backups not running.  (I’m not as diligent with the DEV and TEST servers so yes, sometimes stuff does happen there).

Note: I did add the TSQL scripts for the INSTANCE, DATABASE and BACKUPs to the download section of this blog.  Apparently this code object is not very good at copying out code.

If you’ve been slack with reviewing your backups you might just find some nuggets of interest in the result set from this script…

Stay curious… keep learning…

0 Comments

Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>