Requêtes d'historique des sauvegardes et restaurations SQL Server
Recueil de requêtes sur msdb pour contrôler les dernières sauvegardes et restaurations, l'âge des sauvegardes, la compatibilité de version d'un fichier de sauvegarde et les sauvegardes Azure SQL Database.
Requêtes sur les tables d'historique de msdb pour contrôler la date des dernières sauvegardes et restaurations, l'âge des sauvegardes complètes et des journaux, et la compatibilité de version d'un fichier de sauvegarde. Les noms de bases et les chemins sont des exemples à remplacer.
Dernière restauration de chaque base
Une base sans restauration apparaît quand même, avec des valeurs NULL.
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')
Historique des sauvegardes d'une base (10 derniers jours)
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
Dernière sauvegarde complète et dernière sauvegarde du journal par base
;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;
Dernière sauvegarde complète de chaque base
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
Bases sans sauvegarde depuis 24 heures
--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
Dernière sauvegarde complète avec détails
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
Sauvegardes de toutes les bases des 7 derniers jours
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
Compatibilité de version d'une sauvegarde
Lire l'en-tête d'un fichier de sauvegarde pour connaître la version de SQL Server qui l'a produit :
RESTORE HEADERONLY FROM DISK = N'C:\Backup\data_backup.bak'
| Version | Numéro de version interne | Niveau de compatibilité |
|---|---|---|
| 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 |
Sauvegardes d'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;
Remarques
- La requête « bases sans sauvegarde depuis 24 heures » teste les sauvegardes complètes et les sauvegardes du journal ensemble (
type=DouL). Une sauvegarde du journal récente masque donc l'absence de sauvegarde complète : pour ne contrôler que les complètes, limiter le filtre àtype = 'D'. - Les requêtes de restauration et de sauvegarde adaptent des exemples publiés. Voir les sources ci-dessous.