Backup Report

  • Vijendran

    SSC Veteran

    Points: 278

    Comments posted to this topic are about the item Backup Report

  • dbaInTraining

    Ten Centuries

    Points: 1031

    One suggestion on the script, inside the while block, include the following code:

    /* Need to re-initialize all variables*/

    Select @dbName = null

    , @db_bkpdate = null

    , @media_set_id = Null

    , @backupsize = Null

    , @filepath = Null

    , @filestatus = Null

    , @fileavailable = Null

    , @status = Null

    , @backupsize = Null

    If not, and there are missing backups, some of the data displays incorrectly. Aside from that tiny suggestion, thank you for the code, it is helpful.

  • Vijendran

    SSC Veteran

    Points: 278

    Thanks for the suggestion. Will modify it.

  • Pratap Prabhu

    Say Hey Kid

    Points: 711

    I posted something similar some time ago. Please do check it out and give me your opinion. The physical file check is something I am not doing in my script and I thank you for the idea.

    My script is listed under 'SQL Server Database Backup Status'(http://www.sqlservercentral.com/scripts/sp_send_dbmail/66701/) ,

    I filed it under sp_send_dbmail as it also emails the dbAdmin

  • growl

    Hall of Fame

    Points: 3066

    Hi this is a awesome backup report

    Made a couple of changes to so it did not return blank results and databases that do not require backups. ie tempdb

    --Create a table variable and insert dbid from sysdatabases.

    --Exclude temp database and exclude any databases that are not online

    DECLARE @dbid table

    (rowid INT IDENTITY(1,1),

    mydbid int

    )

    insert into @dbid

    SELECT dbid FROM master..sysdatabases where

    name <> 'tempdb' and

    databasepropertyex(name,'status') ='ONLINE'

    and

    DATABASEPROPERTYEX(name,'IsInStandBy') = 0 --Used for SQL Server 2005/2008 servers only

    order by dbid

    select @svr_name = CAST(SERVERPROPERTY('ServerName')AS sysname)

    DECLARE @RowCount INT

    SET @RowCount = (SELECT COUNT(rowid) FROM @dbid)

    -- Declare an iterator

    DECLARE @I INT

    -- Initialize the iterator

    SET @I = 1

    WHILE (@I <= @RowCount)

    BEGIN

    /* Need to re-initialize all variables*/

    Select @dbName = null , @db_bkpdate = null ,

    @media_set_id = Null , @backupsize = Null ,

    @filepath = Null , @filestatus = Null ,

    @fileavailable = Null , @status = Null , @backupsize = Null,

    @database_id = mydbid

    from @dbid where rowid = @I

    select @dbname = name from master..sysdatabases where dbid = @database_id

    select @db_bkpdate = max(backup_start_date) from msdb..backupset where database_name = @dbname and type='D'

    select @media_set_id = media_set_id from msdb..backupset where backup_start_date = ( select max(backup_start_date) from msdb..backupset where database_name = @dbname and type='D')

    select @backupsize = backup_size from msdb..backupset where backup_start_date = ( select max(backup_start_date) from msdb..backupset where database_name = @dbname and type='D')

    select @filepath = physical_device_name from msdb..backupmediafamily where media_set_id = @media_set_id

    EXEC master..xp_fileexist @filepath , @filestatus out

    if @filestatus = 1

    set @fileavailable = 'Available'

    else

    set @fileavailable = 'NOT Available'

    if (datediff(day,@db_bkpdate,getdate()) > 7)

    set @status = 'Warning'

    else

    set @status = 'Healthy'

    set @backupsize = (@backupsize/1024)/1024

    insert into DBA_TempBackup_details select @svr_name,@dbname,@db_bkpdate,@backupsize,@status,@filepath,@fileavailable

    update DBA_TempBackup_details

    set status = 'Warning' where bkpdate IS NULL

    SET @I = @I + 1

    END

    🙂

  • Vijendran

    SSC Veteran

    Points: 278

    Thank you. I will make a note of this.

  • Adam Wilbur

    SSCertifiable

    Points: 6998

    Thanks, great script... my only change was to return the correct size of backups on servers using backup compression. I basically just changed this line.

    select @backupsize = coalesce(compressed_backup_size, backup_size)



    A.J.
    DBA with an attitude

  • scottd71

    SSC Rookie

    Points: 25

    Very helpful script, thank you for posting.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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