SQL Missed Full Backups Script

Question

Edit: I have it bringing back the info I need, but the where clause is not filtering out all the backups of type 'L' and if I take the parentheses from around the (bak.type = 'D' ...) then then it doesnt filter out all the sysdatabases. Any Idea?

Here is what I have. I was trying to Join msdb..backupset and msdb.dbo.backupmediafamily to get the type as D and the device_type as 2. Any help would be appricated. thanks!

SELECT DISTINCT
    CONVERT(varchar,GETDATE()-1,111) AS Missing_Backup_Date,
    @@servername AS Instance_Name,
    bak.database_name AS Database_Name,
    DATENAME(weekday, getdate()-1) AS Backup_Day_of_Week,
    bak.type AS Failure_Count,
    fam.device_type,
        CASE fam.device_type
            when 2 then 'SQL'
            when 7 then 'Avamar'
        END AS Backup_Type
FROM msdb..backupset bak 
    JOIN msdb.dbo.backupmediafamily fam 
        ON bak.media_set_id = fam.media_set_id
WHERE bak.database_name NOT IN ('tempdb','msdb','master','model')
    AND (bak.type = 'D'
    AND bak.backup_finish_date IS NULL OR bak.backup_finish_date < DATEADD(hour, -24, GETDATE()))

Answer 1

Here is a script that I use to find out what the backup chain is. There is no protection for SQL Injection since it is a administrative script. IE - limit the access.

Just add additional fields and filter.

/******************************************************
 *
 * Name:         usp_get_backup_chain.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     01-21-2014
 *     Blog:     www.craftydba.com
 *
 *     Purpose:  What is the backup chain for a database?
 *
 ******************************************************/

/* 
    Choose the database.
*/

USE [msdb]
GO


/*  
    Drop the old stored procedure.
*/

IF OBJECT_ID('[dbo].[usp_get_backup_chain]') > 0
DROP PROCEDURE [dbo].[usp_get_backup_chain]
GO


/*  
    Create the new stored procedure.
*/

CREATE PROCEDURE [dbo].[usp_get_backup_chain]
    @NAME SYSNAME = '*'
AS
BEGIN

    -- Declare variables
    DECLARE @VAR_TSQL VARCHAR(2048);

    -- Dynamic T-SQL
    SET @VAR_TSQL =
     'SELECT
          s.server_name,
          s.database_name,
          s.name as software_name,
          CASE s.[type]
              WHEN ''D'' THEN ''Database''
              WHEN ''I'' THEN ''Differential database''
              WHEN ''L'' THEN ''Log''
              WHEN ''F'' THEN ''File or filegroup''
              WHEN ''G'' THEN ''Differential file''
              WHEN ''P'' THEN ''Partial''
              WHEN ''Q'' THEN ''Differential partial''
              ELSE ''none''
          END AS backup_type,
          s.backup_start_date,
          s.backup_finish_date,
          f.physical_device_name
      FROM 
          msdb.dbo.backupset AS s 
      JOIN
          msdb.dbo.backupmediafamily as f 
      ON  
          s.media_set_id = f.media_set_id
          ';

    -- All databases?
    IF @NAME <> '*'
        SET @VAR_TSQL = @VAR_TSQL + ' WHERE s.database_name = ' + CHAR(39) + @NAME + CHAR(39);

    -- Show in desc order
    SET @VAR_TSQL = @VAR_TSQL + 'ORDER BY s.backup_start_date DESC ';

    -- Run the command
    EXECUTE(@VAR_TSQL);
END;

GO

If you are really interested in the device type, add the information at the end of the dynamic sql.

Here is the decode per BOl - http://technet.microsoft.com/en-us/library/ms190284.aspx.

      CASE f.device_type
          WHEN 2 THEN 'Disk'
          WHEN 5 THEN 'Tape'
          WHEN 7 THEN 'Virtual device'
          WHEN 105 THEN 'Permanent device'
          ELSE ''
      END AS device_types

Again, this lists for all databases if you do not pass a database name.

What more do you want??