如何找出磁碟上仍然存在的最舊的完整備份？

我想知道的一件事是什麼是仍然存在於我的伺服器中的最舊的完整備份文件。

幾天后刪除這些文件或將這些文件移動到不同的地方是慣例。使用伺服器上的內容，我們可以回到多遠？

在這個測試中，我正在檢查一個名為的數據庫的備份JunoReporting

首先我創建一個函式來測試文件的物理存在

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

然後我用它來 獲取仍在磁碟上的最新完整備份

--=============================================
-- 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 
)

並且還要 在磁碟上獲得最舊的完整備份

--=============================================
-- 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 
)

並 獲取最新備份後的所有備份

--=============================================
-- 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

引用自：https://dba.stackexchange.com/questions/230696