[Backups] Verify your backups physically exist

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