We've been using Rodney Landrum's SSIS package to monitor our environment (As Seen In SQL Server Mag). We're running the old version, which doesn't deal AT ALL with servers not being available. (I assume the latest version does, but haven't had time to check.)
And I came across an issue recently - missing backups. Because of the various retentions set via our backup jobs, we would occasionally have a file vanish. Eek!
So, cue this code. It'll grab the most-recent backup for each server/database, and make sure the file physically exists. It doesn't check the veracity of the backup, just that there's a file there. It also uses xp_fileexists, an undocumented (and therefore it can change - though it's been the same since SQL Server 2000) SP.
If you don't use Rodney's code, you can still use this, but it'll be a _little_ more work. Take the below code, have it run on each machine and dump into a central table (I'll leave those details up to you), then run the second set of code against it.
SELECT server_name,
database_name,
physical_device_name,
backup_start_date,
'FULL' as backup_type
from msdb.dbo.backupmediafamily
inner join msdb.dbo.backupset
on backupset.media_set_id = backupmediafamily.media_set_id
where backup_start_date > getdate()-14
and physical_device_name NOT LIKE 'VDI_%'
and physical_device_name like '%BAK' --or whatever your backups are named.
And here's the full code:
SET NOCOUNT ON
USE DBA_Rep
if object_id('tempdb..#backup_list') is not null
drop table #backup_list;
CREATE TABLE #backup_list (id int IDENTITY, server sysname, database_name sysname, physical_device_name VARCHAR(520), backup_start_date DATETIME, file_exists BIT)
DECLARE @minid INT, @maxid int
DECLARE @does_it_exist INT
DECLARE @filename VARCHAR(500)
--using dba_rep's copy that Rodney Landrum's SSIS code pulls, get a list of the most recent backup for each db in past 2 weeks
INSERT INTO #backup_list
( server ,
database_name ,
physical_device_name,
backup_start_date
)
SELECT Backup_History.server,
Backup_History.database_name,
Backup_History.physical_device_name,
Backup_History.backup_start_date
FROM Backup_History
INNER JOIN
(
SELECT server, database_name,
MAX(backup_start_date) AS max_start_date
FROM Backup_History
WHERE backup_type <>'LOG'
AND backup_start_date > GETDATE()-14
GROUP BY server, database_name
)most_recent
ON most_recent.SERVER = Backup_History.Server
AND most_recent.database_name = Backup_History.database_name
AND most_recent.max_start_date = Backup_History.backup_start_date
AND Backup_History.physical_device_name NOT LIKE 'SQLsafe%'
AND Backup_History.backup_type <> 'LOG'
--Fixing the names of local backups so that we can get them over the network.
UPDATE #backup_list
SET physical_device_name = REPLACE(physical_device_name,LEFT(physical_device_name,2), '\\' + LTRIM(RTRIM(server)) + '\' + LEFT(physical_device_name,1) + '$')
WHERE physical_device_name LIKE '%:%'
SELECT @minid = MIN(id) , @maxid = MAX(id) FROM #backup_list
--Walk the list, checking each file and updating the table
WHILE @minid < @maxid
BEGIN
SET @does_it_exist = 0
SELECT @filename = physical_device_name
FROM #backup_list
WHERE id = @minid
EXEC Master.dbo.xp_fileexist @filename, @does_it_exist OUTPUT
UPDATE #backup_list
SET file_exists = @does_it_exist
WHERE id = @minid
IF @minid % 10 = 0 PRINT @minid
SET @minid = @minid+1
END
SELECT * FROM #backup_list WHERE file_exists = 0
