How to find out the oldest full backup still existing on disk?

Question

one of the things I like to find out is what is the oldest full backup file still living in my servers.

it is the custom to delete or move those files to a different place after a number of days. How far can we go back in time, with what we have on the server?

Answer 1

In this test I am checking the backups for a database called JunoReporting

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

USE MASTER
GO

create FUNCTION dbo.fn_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

then I use it to get the latest Full backup still on Disk

--=============================================
-- get the latest Full backup still on Disk
--=============================================

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
  ,compressed_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' ELSE 'No' END
  FROM msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
         ON a.media_set_id = b.media_set_id

  WHERE 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
                               WHERE 1=1
                                 AND f.[type] = 'D'
                                 AND f.DATABASE_NAME='Junoreporting'
                                 AND master.dbo.fn_FileExists(b.physical_device_name) = 1 
)

and also to get the OLDEST Full backup still on Disk

--=============================================
-- get the OLDEST Full backup still on Disk
--=============================================

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
  ,compressed_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' ELSE 'No' END
  FROM msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
         ON a.media_set_id = b.media_set_id

  WHERE 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
                               WHERE 1=1
                                 AND f.[type] = 'D'
                                 AND f.DATABASE_NAME='Junoreporting'
                                 AND master.dbo.fn_FileExists(b.physical_device_name) = 1 
)

and also Get all the backups after the latest backup

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


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
  ,compressed_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' ELSE 'No' END
  FROM msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
         ON a.media_set_id = b.media_set_id

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

                            SELECT MAX(f.backup_set_id) 
                              FROM msdb.dbo.backupset f 
                             WHERE 1=1
                               AND f.[type] = 'D'
                               AND f.DATABASE_NAME='Junoreporting'
                               AND master.dbo.fn_FileExists(b.physical_device_name) = 1 

                        )

  --and a.[type] = 'L'
  AND a.DATABASE_NAME='Junoreporting'
  AND b.[Device_Type] = 2
  AND a.is_copy_only = 0
order by backup_set_id desc