SQL Server backup and restore history queries
Collection of msdb queries to check the last backups and restores, backup age, the version compatibility of a backup file and Azure SQL Database backups.
Queries on the msdb history tables to check the date of the last backups and restores, the age of full and log backups, and the version compatibility of a backup file. Database names and paths are examples to replace.
Last restore of each database
A database without any restore still appears, with NULL values.
WITH LastRestores AS
(
SELECT
DatabaseName = [d].[name] ,
[d].[create_date] ,
[d].[compatibility_level] ,
[d].[collation_name] ,
r.*,
RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1 and DatabaseName in ('Database1','Database2')
Backup history of one database (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 'L' THEN 'Log'
END AS backup_type
, msdb.dbo.backupset.is_copy_only
, msdb.dbo.backupset.backup_size AS 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
, CAST(first_lsn AS VARCHAR) AS first_lsn
, CAST(last_lsn AS VARCHAR) AS last_lsn
, CAST(checkpoint_lsn AS VARCHAR) AS checkpoint_lsn,
CAST(database_backup_lsn as VARCHAR) AS database_backup_lsn
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 database_name = 'DatabaseName'
--AND msdb..backupset.type ='D'
ORDER BY
--backup_finish_date desc
msdb.dbo.backupset.database_name,
backup_finish_date desc
Last full and last log backup of each 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,
is_copy_only,
rownum =
row_number() over
(
partition by database_name, type
order by backup_finish_date desc
)
from msdb.dbo.backupset
)
select
database_name,
backup_type,
backup_finish_date,
is_copy_only
from backup_cte
where rownum = 1
order by database_name;
Most recent full backup of each database
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name
Databases without a backup in the last 24 hours
--Databases with data backup over 24 hours old
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.type = 'D' or msdb.dbo.backupset.type = 'L'
GROUP BY msdb.dbo.backupset.database_name
HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))
UNION
--Databases without any backup history
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
master.dbo.sysdatabases.NAME AS database_name,
NULL AS [Last Data Backup Date],
9999 AS [Backup Age (Hours)]
FROM
master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
ON master.dbo.sysdatabases.name = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'
ORDER BY
msdb.dbo.backupset.database_name
Most recent full backup of each database, with details
SELECT
A.[Server],
A.last_db_backup_date,
B.backup_start_date,
B.expiration_date,
B.backup_size,
B.logical_device_name,
B.physical_device_name,
B.backupset_name,
B.description
FROM
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
) AS A
LEFT JOIN
(
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,
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 msdb..backupset.type = 'D'
) AS B
ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
A.database_name
Backups of all databases over the last 7 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 '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() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
Backup version compatibility
Read the header of a backup file to find the SQL Server version that produced it:
RESTORE HEADERONLY FROM DISK = N'C:\Backup\data_backup.bak'
| Version | Internal version number | Compatibility level |
|---|---|---|
| SQL Server 2019 | 895 - 904 | 150 |
| SQL Server 2017 | 868 - 869 | 140 |
| SQL Server 2016 | 852 | 130 |
| SQL Server 2014 | 782 | 120 |
| SQL Server 2012 | 706 | 110 |
Backups of Azure SQL Database
SELECT db.name
, backup_start_date
, backup_finish_date
, CASE backup_type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType
, CASE in_retention
WHEN 1 THEN 'In Retention'
WHEN 0 THEN 'Out of Retention'
END AS is_Backup_Available
FROM sys.dm_database_backups AS ddb
INNER MERGE JOIN sys.databases AS db
ON ddb.physical_database_name = db.physical_database_name
ORDER BY backup_start_date DESC;
Notes
- The "databases without a backup in the last 24 hours" query tests full and log backups together (
type=DorL). A recent log backup therefore hides a missing full backup: to check full backups only, restrict the filter totype = 'D'. - The restore and backup queries adapt published examples. See the sources below.