info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



SQL Server Get Latest Database or Log Backup History
by BF (Principal Consultant; Architecture; Engineering)
2017-01-27









Solution:


Backup History:


--List Latest Backup Per Database
;with backup_cte as
(
select
database_name,
backup_type =
case type
when 'D' then 'Database'
when 'L' then 'Log'
when 'I' then 'Differential'
else 'Other'
end,
backup_finish_date,
physical_device_name,
rownum =
row_number() over
(
partition by database_name, type
order by backup_finish_date desc
)

FROM
msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
)
select
@@servername as 'Server_Name',
database_name as 'Database_Name',
Backup_Type,
Backup_Finish_Date,
Physical_Device_Name
from backup_cte
where rownum = 1
order by database_name;




--List all Full Backups in the last 10 days
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM
msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 10)
AND
backupset.type = 'D'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date


--List all Differential Backups in the last 10 days
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM
msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 10)
AND
backupset.type = 'I'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date



--List all Log Backups in the last 1 day
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM
msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
AND
backupset.type = 'L'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date DESC



--List any databases not backed recently

;WITH CTE_Backup AS
(
SELECT database_name,backup_start_date,type,is_readonly,physical_device_name
,Row_Number() OVER(PARTITION BY database_name
ORDER BY backup_start_date DESC) AS RowNum
FROM msdb..backupset BS
JOIN msdb.dbo.backupmediafamily BMF
ON BS.media_set_id=BMF.media_set_id
)
SELECT D.name
,ISNULL(CONVERT(VARCHAR,backup_start_date),'No backups') AS last_backup_time
,D.recovery_model_desc
,state_desc
,physical_device_name
FROM sys.databases D
LEFT JOIN CTE_Backup CTE
ON D.name = CTE.database_name
AND RowNum = 1
WHERE ( backup_start_date IS NULL OR backup_start_date < DATEADD(dd,-10,GetDate()) )
ORDER BY D.name,type



--List any missing transaction log backups

;WITH CTE_Backup AS
(
SELECT database_name,backup_start_date,type,is_readonly,physical_device_name
,Row_Number() OVER(PARTITION BY database_name,BS.type
ORDER BY backup_start_date DESC) AS RowNum
FROM msdb..backupset BS
JOIN msdb.dbo.backupmediafamily BMF
ON BS.media_set_id=BMF.media_set_id
WHERE type='L'
)
SELECT D.name
,ISNULL(CONVERT(VARCHAR,backup_start_date),'No log backups') AS last_backup_time
,D.recovery_model_desc
,state_desc
,physical_device_name
FROM sys.databases D
LEFT JOIN CTE_Backup CTE
ON D.name = CTE.database_name
AND RowNum = 1
WHERE ( backup_start_date IS NULL OR backup_start_date < DATEADD(dd,-1,GetDate()) )
AND recovery_model_desc != 'SIMPLE'
ORDER BY D.name,type


SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date,
CAST(msdb.dbo.backupset.backup_size AS NUMERIC(35,2))/1048576.0 AS backup_size_MB
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb.dbo.backupset.type = 'D'
ORDER BY
msdb.dbo.backupset.backup_finish_date desc



WITH backups_list AS
(
Select
ROW_NUMBER() Over (Partition By b.database_name, b.type Order By b.database_name ASC, b.backup_finish_date DESC) AS 'RowNumber',
d.name AS [database_name],
b.backup_set_id,
b.media_set_id,
b.type AS backup_type,
b.backup_finish_date,
d.recovery_model_desc,
b.user_name,
b.machine_name,
b.server_name
From Master.sys.databases AS d
Left Join Msdb.dbo.[backupset] AS b on d.name = b.database_name
)
Select
UPPER(bl.database_name) as 'DB',
bl.recovery_model_desc as 'RecoveryModel',
COALESCE(CONVERT(varchar(25),bl.backup_finish_date),'-') as'BackupFinish',
CASE bl.backup_type WHEN 'D' THEN 'DB' WHEN 'I' THEN 'DIFF DB' WHEN 'L' THEN 'LOG' ELSE '-' END AS BackupType,
COALESCE(CONVERT(varchar(10),(DATEDIFF(day,bl.backup_finish_date, GETDATE()))),'-') AS 'Last_Backup_Days',
COALESCE(CONVERT(varchar(10),(DATEDIFF(hour,bl.backup_finish_date, GETDATE()))),'-') AS 'Last_Backup_Hours',
COALESCE(CONVERT(varchar(10),(DATEDIFF(minute,bl.backup_finish_date,GETDATE()))),'-') AS 'Last_Backup_Mins',
COALESCE(CONVERT(varchar(25),m.physical_device_name),'-') as 'PhysicalDevice',
COALESCE(CONVERT(varchar(25),bl.server_name),'-') as 'Server',
COALESCE(CONVERT(varchar(25),bl.user_name),'-') as 'UserName',
Physical_Device_Name
From backups_list bl
Left Join msdb.dbo.backupmediafamily m ON bl.media_set_id = m.media_set_id
Where (bl.RowNumber = 1 and m.family_sequence_number = 1) or bl.backup_set_id is null
Order By bl.backup_finish_date DESC, bl.database_name ASC







Last Full Backup Duration:


;WITH CTE_Backup AS
(
SELECT database_name,backup_start_date,backup_finish_date,type,physical_device_name
,Row_Number() OVER(PARTITION BY database_name,BS.type
ORDER BY backup_start_date DESC) AS RowNum
FROM msdb..backupset BS
JOIN msdb.dbo.backupmediafamily BMF
ON BS.media_set_id=BMF.media_set_id
)
SELECT UPPER(D.name) As 'DB Name'
,ISNULL(CONVERT(VARCHAR,backup_start_date),'No backups') AS 'Last_Backup_Start_Time'
,ISNULL(CONVERT(VARCHAR,backup_finish_date),'No backups') AS 'Last_Backup_Finish_Time'
,CONVERT(TIME,backup_finish_date - backup_start_date) as 'Elapsed Time(H:M:S)'
,D.Recovery_Model_Desc
,State_desc,
CASE WHEN type ='D' THEN 'Full database'
WHEN type ='I' THEN 'Differential database'
WHEN type ='L' THEN 'Log'
WHEN type ='F' THEN 'File or filegroup'
WHEN type ='G' THEN 'Differential file'
WHEN type ='P' THEN 'Partial'
WHEN type ='Q' THEN 'Differential partial'
ELSE 'Unknown' END AS 'Backup_Type'
,Physical_Device_Name
FROM sys.databases D
LEFT JOIN CTE_Backup CTE ON D.name = CTE.database_name
AND RowNum = 1
AND [type] = 'D'
ORDER BY 'Elapsed Time(H:M:S)' Desc



Restore History:

SELECT
rh.destination_database_name AS [Database],
CASE WHEN rh.restore_type = 'D' THEN 'Database'
WHEN rh.restore_type = 'F' THEN 'File'
WHEN rh.restore_type = 'I' THEN 'Differential'
WHEN rh.restore_type = 'L' THEN 'Log'
ELSE rh.restore_type
END AS [Restore Type],
rh.restore_date AS [Restore Date],
bmf.physical_device_name AS [Source],
rf.destination_phys_name AS [Restore File],
rh.user_name AS [Restored By]
FROM msdb.dbo.restorehistory rh
INNER JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
ORDER BY rh.restore_history_id DESC
GO

SELECT [rs].[destination_database_name],
[rs].[restore_date],
rs.user_name,
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore],
*
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
--Where bs.database_name in ('')
ORDER BY bs.database_name asc