To see MSSQL history Query, run this SQL query. It will show all backups between the 1 january 2021 and 1 february 2021, order by Database and then Last backup time.
It will also show if the backup is a CopyOnly job.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
;WITH CTE_Backup AS ( SELECT database_name,is_copy_only,backup_start_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 AND BS.backup_start_date >= '01/01/2021' AND BS.backup_start_date <= '02/02/2021' ) SELECT D.name ,ISNULL(CONVERT(VARCHAR,backup_start_date),'No backups') AS last_backup_time ,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,CTE.is_copy_only FROM sys.databases D LEFT JOIN CTE_Backup CTE ON D.name = CTE.database_name ORDER BY D.name,last_backup_time |