Server Backup History Report (updated 2006-04-11)

  • mops33

    SSC Journeyman

    Points: 90

    Comments posted to this topic are about the item Server Backup History Report (updated 2006-04-11)

  • Jpotucek

    SSCoach

    Points: 18583

    This procedure works GREAT - but it only give the most recent backup.. not a full History...

    my output attached.

    Does anyone know why I am only getting one days worth of history???

  • m.rana.reddy

    Grasshopper

    Points: 15

    hey, were you able to get a complete history of the backups using this procedure as against only one day's backup history?

  • Nadrek

    SSCoach

    Points: 19979

    For anyone looking at this now:

    A) This does also work on SQL Server 2005, 2008 and 2008 R2, both 32 and 64 bit.

    B) This is, of course, subject to the "overwritten backup devices (files) don't update msdb.dbo.backupmediafamily physical device name (location) bug in SQL Server 2005 and below:

    http://connect.microsoft.com/SQLServer/feedback/details/306604/physical-device-name-not-captured-correctly-in-msdb-dbo-backupmediafamily

    C) I've rolled in gabriel.defigueiredo's update to use xp_fileexist instead of xp_cmdshell; not only does it work when xp_cmdshell is disabled (verified on a 2008 box), I've tested it as being on the order of 5 times faster, as well (this becomes important when you remove the "only show the latest" join, to show all backups).

    -- Originally from http://www.sqlservercentral.com/Forums/Topic440139-146-1.aspx and http://www.sqlservercentral.com/Forums/Topic705510-599-1.aspx

    /*

    Server Backup History Report

    (Full DB Backups only)

    1. lists all databases with no backup history

    2. lists last backup for other databases

    includes Date, User, Size, Duration, Age, Finish Date & Location

    Includes system databases

    Excludes TempDB

    Excludes backup history data where backupmediafamily.device_type = 7

    these are typically created by Veritas BackupExec

    Tested on SQL Server 2000 (SP3)

    Brett Ivery 24/03/2006 created

    11/04/2006 added file existence check

    Other: 20110222 updated to not use xp_cmdshell, per http://www.sqlservercentral.com/Forums/Topic705510-599-1.aspx

    */

    SET nocount ON

    USE tempdb

    -- drop temp proc if exists

    IF object_id('tempdb..#prFileExists') IS NOT NULL

    DROP PROCEDURE #prFileExists

    GO

    -- START create temp proc to check backup file existence

    CREATE PROCEDURE dbo.#prFileExists

    @path varchar(300) ,

    @p2 int OUTPUT

    AS BEGIN

    IF object_id('tempdb..#FileExists') IS NOT NULL

    DROP TABLE #FileExists

    CREATE TABLE #FileExists ([File Exists] int, [File is a Directory] int, [Parent Directory Exists] int)

    INSERT INTO #FileExists exec master..xp_fileexist @path

    SELECT @p2 =[File Exists] from #FileExists

    END

    GO

    -- END create temp proc to check backup file existence

    --Declare variables and temp table for existing backup data

    DECLARE @i int

    DECLARE @EXISTS bit

    DECLARE @location varchar(260)

    SET @i = 0

    DECLARE @Tmp TABLE (

    [ID] [int] identity(1,1) NOT NULL,

    [FileExists] bit DEFAULT 0,

    [DBName] [varchar] (30) NULL ,

    [UserName] [varchar] (30) NULL ,

    [BackupSize] [varchar] (13) NULL ,

    [Duration] [varchar] (10) NULL ,

    [BackupAge] [int] NULL ,

    [FinishDate] [varchar] (20) NULL ,

    [Location] [varchar] (260) NULL ,

    [device_type] [tinyint] NULL

    )

    -- return data about databases with no backup history

    PRINT ' ================================================================================================================================'

    PRINT ' ' + @@servername + ' - Database Backup History (SQL Backups only)'

    PRINT ' '

    if exists(

    SELECT

    DB.Name

    FROM

    master..sysdatabases DB

    left join

    (

    select database_name

    from msdb..BACKUPSET BS

    join msdb..backupmediaset MS

    on

    BS.media_set_id = MS.media_set_id

    join msdb..backupmediafamily MF

    on

    BS.media_set_id = MF.media_set_id

    WHERE

    type = 'D'

    and mf.device_type <> 7

    group by database_name

    ) BS

    on BS.database_name = DB.name

    where

    BS.Database_name is null

    and

    not DB.Name = 'TempDB'

    )

    SELECT

    DB.Name as [Databases With No Backup History]

    FROM

    master..sysdatabases DB

    left join

    (

    select database_name

    from msdb..BACKUPSET BS

    join msdb..backupmediaset MS

    on

    BS.media_set_id = MS.media_set_id

    join msdb..backupmediafamily MF

    on

    BS.media_set_id = MF.media_set_id

    WHERE

    type = 'D'

    and mf.device_type <> 7

    group by database_name

    ) BS

    on BS.database_name = DB.name

    where

    BS.Database_name is null

    and

    not DB.Name = 'TempDB'

    else

    SELECT

    '- None -' as [Databases With No Backup History]

    -- get existing backup history data

    -- (into table variable for later modification)

    PRINT ' Databases With Backup History'

    PRINT ' -------------------------------- '

    INSERT @Tmp(

    [DBName]

    , [UserName]

    , [BackupSize]

    , [Duration]

    , [BackupAge]

    , [FinishDate]

    , [Location]

    , [device_type]

    )

    SELECT

    cast(database_name AS varchar(30)) AS [DBName],

    cast(user_name AS varchar(30)) AS [UserName],

    str(cast(backup_size AS decimal(20,2)) / 1048576 ,10,2) + ' MB' AS [BackupSize],

    cast(datediff(n,backup_start_date,backup_finish_date) AS varchar(5)) + ' min.' AS [Duration],

    cast(datediff(dd,backup_finish_date,Getdate()) AS varchar(10)) AS [BackupAge],

    convert(varchar(20),backup_finish_date) AS [FinishDate],

    physical_device_name AS [Location],

    mf.device_type

    FROM

    master..sysdatabases DB

    JOIN

    msdb..BACKUPSET BS

    ON DB.name = BS.database_name

    JOIN msdb..backupmediaset MS

    ON

    BS.media_set_id = MS.media_set_id

    JOIN msdb..backupmediafamily MF

    ON

    BS.media_set_id = MF.media_set_id

    JOIN

    (

    SELECT

    max(backup_set_id) AS backup_set_id

    FROM

    msdb..BACKUPSET BS

    JOIN msdb..backupmediaset MS

    ON

    BS.media_set_id = MS.media_set_id

    JOIN msdb..backupmediafamily MF

    ON

    BS.media_set_id = MF.media_set_id

    WHERE

    type = 'D'

    AND mf.device_type <> 7

    GROUP BY database_name

    ) MaxBackup

    ON

    BS.backup_set_id = MaxBackup.backup_set_id

    WHERE

    type = 'D'

    -- loop through the results and update the FileExists field

    -- (calling temp proc for each row)

    SELECT @i = min(ID) FROM @Tmp WHERE ID > @i

    WHILE @i IS NOT NULL BEGIN

    IF @i IS NOT NULL BEGIN

    -- PRINT cast(@i AS varchar(20))

    SET @location = (SELECT location FROM @Tmp WHERE ID = @i)

    EXECUTE #prFileExists @location, @EXISTS OUTPUT

    UPDATE @Tmp SET FileExists = @EXISTS WHERE ID = @i

    END

    SELECT @i = min(ID) FROM @Tmp WHERE ID > @i

    END

    -- return the results

    SELECT

    [DBName]

    , [UserName]

    , [BackupSize]

    , [Duration]

    , [BackupAge] AS [BackupAge (Days)]

    , [FinishDate]

    , [FileExists]

    , [Location]

    FROM

    @Tmp

    ORDER BY

    [BackupAge] DESC

    -- drop the temp proc

    IF object_id('tempdb..#prFileExists') IS NOT NULL

    DROP PROCEDURE #prFileExists

    GO

    PRINT ' ================================================================================================================================'

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply