Retorna os últimos backups gerados SQL Server

GitHub

-- retorna os ultimos backups gerados nos 10 ultimos dias

Use msdb

GO

SET NOCOUNT ON

GO

DECLARE @Dias Int

Set @Dias = 10

SELECT

S.Database_Name

,M.Physical_Device_Name

,Convert(Decimal(12,2), S.Backup_Size / 1024 / 1024) As Size

,S.Backup_Start_Date

,S.Backup_Finish_Date

,Cast(DateDiff(Second, S.Backup_Start_Date , S.Backup_Finish_Date) As Varchar(4)) As Seconds_Duration

,Case S.Type

When 'D' Then 'Full'

When 'I' Then 'Differential'

When 'L' Then 'Transaction Log'

End As BackupType

,S.Server_Name

FROM

msdb.dbo.BackupSet S

JOIN

msdb.dbo.BackupMediaFamily M

ON

S.Media_Set_ID = M.Media_Set_ID

WHERE

S.Database_Name In (SELECT Name FROM Sys.Databases)

AND S.Backup_Start_Date > Convert(Char(10), (DateAdd(Day, - @Dias, GetDate())), 121)

-- Para listar todos os databases sem o parametro de dias,

-- comente a linha as duas linhas acima (S.Database... e S.Back...) e troque pelas linhas abaixo.

/*

S.Database_Name = 'MyDataBase'

*/

ORDER BY

S.Backup_Start_Date DESC, S.Backup_Finish_Date

--resultado: