Backup Report

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Vijendran R » Backup Report

Backup Report

Rate Topic

Display Mode

Topic Options

Author

Message

vijendran

Posted Tuesday, July 21, 2009 6:31 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 22, 2013 9:46 AM
Points: 84, Visits: 421

Comments posted to this topic are about the item Backup Report

Post #756520

dbaInTraining

Posted Tuesday, August 11, 2009 10:55 AM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:34 AM
Points: 740, Visits: 211

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.

Post #768815

vijendran

Posted Thursday, August 20, 2009 3:14 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 22, 2013 9:46 AM
Points: 84, Visits: 421

Thanks for the suggestion. Will modify it.

Post #774071

Pratap Prabhu

Posted Monday, November 23, 2009 2:54 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 03, 2013 9:38 PM
Points: 139, Visits: 148

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

Post #823535

growl

Posted Monday, February 01, 2010 10:16 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 11:17 PM
Points: 423, Visits: 531

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

Smile

Post #857720

vijendran

Posted Sunday, February 21, 2010 9:06 PM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 22, 2013 9:46 AM
Points: 84, Visits: 421

Thank you. I will make a note of this.

Post #870025

A.J. Wilbur

Posted Wednesday, March 30, 2011 10:18 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:46 PM
Points: 1,002, Visits: 512

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

Post #1086330

scottd71

Posted Thursday, April 26, 2012 2:42 PM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:57 PM
Points: 1, Visits: 61

Very helpful script, thank you for posting.

Post #1291139

« Prev Topic | Next Topic »

Permissions