Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup Report Expand / Collapse
Author
Message
Posted Tuesday, July 21, 2009 6:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 19, 2013 5:44 AM
Points: 84, Visits: 424
Comments posted to this topic are about the item Backup Report
Post #756520
Posted Tuesday, August 11, 2009 10:55 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:24 PM
Points: 764, Visits: 228
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
Posted Thursday, August 20, 2009 3:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 19, 2013 5:44 AM
Points: 84, Visits: 424
Thanks for the suggestion. Will modify it.
Post #774071
Posted Monday, November 23, 2009 2:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 3, 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
Posted Monday, February 1, 2010 10:16 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 10:06 PM
Points: 423, Visits: 534
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








Post #857720
Posted Sunday, February 21, 2010 9:06 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 19, 2013 5:44 AM
Points: 84, Visits: 424
Thank you. I will make a note of this.
Post #870025
Posted Wednesday, March 30, 2011 10:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:35 PM
Points: 1,011, Visits: 564
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
Posted Thursday, April 26, 2012 2:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 23, 2014 8:30 AM
Points: 1, Visits: 114
Very helpful script, thank you for posting.
Post #1291139
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse