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
