July 21, 2009 at 6:31 am
Comments posted to this topic are about the item Backup Report
August 11, 2009 at 10:55 am
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.
August 20, 2009 at 3:14 am
Thanks for the suggestion. Will modify it.
November 23, 2009 at 2:54 pm
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
February 1, 2010 at 10:16 pm
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
🙂
February 21, 2010 at 9:06 pm
Thank you. I will make a note of this.
March 30, 2011 at 10:18 am
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
April 26, 2012 at 2:42 pm
Very helpful script, thank you for posting.
May 18, 2016 at 6:58 am
Thanks for the script.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply