Où sont mes backups ?

Par Arian Papillon le jeudi 4 avril 2013, 19:22 - Lien permanent

SQL Server stocke un historique des backups effectués dans les tables système de MSDB.

Il est utile d'interroger ces tables, par exemple pour savoir de quand date la dernière sauvegarde, à quel emplacement le fichier de backup a-t-il été créé...) : on peut avoir des surprises !

La requête suivante vous permet d'obtenir rapidement cette information :

  • Quelles bases sont sauvegardées ou non
  • Où se trouve le device de la dernière sauvegarde : fichier disque ou device réseau
  • Et ces informations de backup même si une base a été supprimée (ou renommée)
/*-------------------------------------------------------------------
[SCRIPT]   
[DATABASE] 
[DESCRIPTION] Derniers fichiers ou devices de sauvegarde full
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ]  5/04/2013
-------------------------------------------------------------------*/
SELECT   CASE WHEN sd.NAME IS NULL THEN '*** Backup of non existant db : ' ELSE '' END  + COALESCE(sd.name,filebackup.database_name) AS database_name
        ,filebackup.backup_date
              ,filebackup.type
          ,filebackup.physical_device_name
          ,filebackup.device_type
FROM     master.sys.databases sd
         FULL JOIN ( SELECT   bk.database_name
                             ,MAX(bk.backup_finish_date) AS backup_date
                             ,bm.device_type
                     FROM     msdb..backupset bk
                              JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id
                     WHERE    bk.type = 'D' 
                                   -- AND bm.device_type = 2 -- pour fichiers disque
                     GROUP BY bk.database_name
                             ,bm.device_type
                   ) AS lastbackup
         INNER JOIN ( SELECT  bk.database_name
                       ,bk.backup_finish_date AS backup_date
                       ,bk.type
                       ,bm.physical_device_name
                       ,device_type
                FROM    msdb..backupset bk
                        JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id
                WHERE   bk.type = 'D'
              ) AS filebackup ON lastbackup.database_name = filebackup.database_name
                                 AND lastbackup.backup_date = filebackup.backup_date
         ON sd.name = filebackup.database_name
             ORDER BY sd.name,backup_date