SQL Server – How to find the oldest full backup still existing on disk?

In this test, I check the backups of a database called JunoReporting

I first create a function to test the physical existence of the file

USE THE MASTER
GO

Create a function dbo.fn_FileExists (@path varchar (8000))
RETURNS
AS
TO START
DECLARE @result INT
EXEC master.dbo.xp_fileexist @path, @result OUTPUT
BACK casting (@ result as bit)
END;
GO

so I use it for
get the last full backup still on the disk

- ===========================================
- get the last full backup still on the disk
- ===========================================

to select
a.backup_set_id AS [LATEST Full backup still on Disk]
  , a.media_set_id
, a.backup_start_date
, a.backup_finish_date
,a type
, backup_size = a.backup_size / 1024.00
, a.is_copy_only
, comprim_backup_size = a.compressed_backup_size
-, b.media_set_id
, b.physical_device_name
,[File Exists]= CASE WHEN master.dbo.fn_FileExists (b.physical_device_name) = 1 THEN "YES", EXCEPT "NO", END
FROM msdb.dbo.backupset a
INNER JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id

O 1 = 1
and a.backup_set_id = (
SELECT MAX (f.backup_set_id)
FROM msdb.dbo.backupset f
INNER JOIN msdb.dbo.backupmediafamily b
ON f.media_set_id = b.media_set_id
O 1 = 1
And F.[type] = & # 39; D & # 39;
AND f.DATABASE_NAME = Junoreporting & # 39;
AND master.dbo.fn_FileExists (b.physical_device_name) = 1
)

and also to
get the oldest full backup still on the disk

- ===========================================
- get the oldest full backup still on the disk
- ===========================================

to select
a.backup_set_id AS [OLDEST Full backup still on Disk]
  , a.media_set_id
, a.backup_start_date
, a.backup_finish_date
,a type
, backup_size = a.backup_size / 1024.00
, a.is_copy_only
, comprim_backup_size = a.compressed_backup_size
-, b.media_set_id
, b.physical_device_name
,[File Exists]= CASE WHEN master.dbo.fn_FileExists (b.physical_device_name) = 1 THEN "YES", EXCEPT "NO", END
FROM msdb.dbo.backupset a
INNER JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id

O 1 = 1
and a.backup_set_id = (
SELECT MIN (f.backup_set_id)
FROM msdb.dbo.backupset f
INNER JOIN msdb.dbo.backupmediafamily b
ON f.media_set_id = b.media_set_id
O 1 = 1
And F.[type] = & # 39; D & # 39;
AND f.DATABASE_NAME = Junoreporting & # 39;
AND master.dbo.fn_FileExists (b.physical_device_name) = 1
)

and also
Get all backups after the last backup

- ===========================================
- Get all backups after the last backup
- ===========================================


to select
a.backup_set_id
, a.media_set_id
, a.backup_start_date
, a.backup_finish_date
,a type
, backup_size = a.backup_size / 1024.00
, a.is_copy_only
, comprim_backup_size = a.compressed_backup_size
-, b.media_set_id
, b.physical_device_name
,[File Exists]= CASE WHEN master.dbo.fn_FileExists (b.physical_device_name) = 1 THEN "YES", EXCEPT "NO", END
FROM msdb.dbo.backupset a
INNER JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id

O 1 = 1
and a.backup_set_id> = (- the last full backup

SELECT MAX (f.backup_set_id)
FROM msdb.dbo.backupset f
O 1 = 1
And F.[type] = & # 39; D & # 39;
AND f.DATABASE_NAME = Junoreporting & # 39;
AND master.dbo.fn_FileExists (b.physical_device_name) = 1

)

--and a.[type] = & # 39; L & # 39;
AND a.DATABASE_NAME = & # 39; Junoreporting & # 39;
And B.[Device_Type] = 2
AND a.is_copy_only = 0
order by backup_set_id desc

enter the description of the image here