Verify that your backups are archived.
If you backup your databases to a disk file, you should make sure that these disk files are then being archived to tape. Without this, your ability to recover the database is compromised in the event of a disaster. This script will examine the "archive" flag on all of the backup files to make sure the system administrators have really archived them. It is set to examine all backups for the past three days but can be easily changed to be more or less. Any files which have NOT been archived (if any) will be kept in a temporary file. Once finished, you can then save this information to a repository, text file, e-mail, etc.
-- create the temp file where any non-archived files will be listed
create table #arch_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;
-- declarations...
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); -- this will determine how far back in history to look
-- loop thru the backups selected
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 #arch_backups (physical_name)
exec ('exec master.dbo.xp_cmdshell ''dir "' + @physical_name + '" /a:a /b''');
delete from #arch_backups where physical_name is null or physical_name in('File Not Found');
update #arch_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;
-- view the results
select * from #arch_backups; 
