2

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?

4

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.