Search

Monday, July 7, 2014

Backup History

Here is a useful script to get the backup History for all databases in SQL Server:

SELECT S.Server_Name, S.Recovery_Model, S.Database_Name, M.Physical_Device_Name,
    CASE S.[Type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType,
    S.Backup_Start_Date, S.Backup_finish_Date,
    CAST(DATEDIFF(second, S.Backup_Start_Date,S.Backup_Finish_Date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeElapsed,
    CAST(CAST(S.Backup_Size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Backup_Size,
    CAST(S.Dirst_LSN AS VARCHAR(50)) AS First_LSN,
    CAST(S.Last_LSN AS VARCHAR(50)) AS Last_LSN
FROM msdb.dbo.BackupMediaFamily AS M 
INNER JOIN msdb.dbo.BackupSet AS S ON M.Media_Set_ID = S.Media_Set_ID
ORDER BY S.Backup_Start_Date DESC



No comments:

Post a Comment