Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



SQL Server Get Latest Database or Log Backup History
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-01-27









Solution:


Backup History:


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'
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 ASC, bl.database_name ASC






Restore History:

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