LLI Wiki — My Digital Knowledge Base

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

Sources

Mastodon LLIMastodon SocialFosstodon