Dennis Stephani (1/2/2009)

---

Also try the following code. We had some issues where our tape backup system missed archiving some DB backup files so I scheduled the following code to check for files with the "archive" bit set. I cut this code out of a larger set of code and didn't test it -so I may have missed a chunk, but you get the picture. In the larger picture, this process would send an e-mail to the DBA team if anything wasn't archived (any results in #hb_backups).

P.S. Our server admin team switched to Tivoli backup system about 18 months ago which rendered this check useless (Tivoli doesn't change the archive bit flag).

P.P.S. We've since disabled the use of "xp_cmdshell" for security reasons - so this code would no longer work anyway.

create table #hb_backups

( database_name nvarchar(128),

backup_type char(1),

backup_start_date datetime,

backup_finish_date datetime,

backup_file_name nvarchar(200),

physical_name nvarchar(1000)

)

set nocount on

declare @database_name nvarchar(128), @backup_type char(1), @backup_start_date datetime, @backup_finish_date datetime, @physical_name nvarchar(260)

declare backup_cur cursor for

select mf.physical_device_name, s.type, s.database_name, s.backup_start_date, s.backup_finish_date

from msdb.dbo.backupmediafamily mf inner join msdb.dbo.backupset s on mf.media_set_id = s.media_set_id

where s.backup_start_date > (getdate() - 3)

open backup_cur

fetch next from backup_cur into @physical_name, @backup_type, @database_name, @backup_start_date, @backup_finish_date

while @@fetch_status = 0

begin

insert into #hb_backups (physical_name)

exec ('exec master.dbo.xp_cmdshell ''dir "' + @physical_name + '" /a:a /b''')

delete from #hb_backups where physical_name is null or physical_name in('File Not Found')

update #hb_backups set

database_name = @database_name,

backup_type = @backup_type,

backup_start_date = @backup_start_date,

backup_finish_date = @backup_finish_date,

backup_file_name = @physical_name

where database_name is null

fetch next from backup_cur into @physical_name, @backup_type, @database_name, @backup_start_date, @backup_finish_date

end

close backup_cur

deallocate backup_cur