Obtenir l’historique des sauvegardes de bases de données

Publié le par

Voici quelques requêtes simples qui montrent l’historique de toutes les sauvegardes de toutes les bases de données ou d’une base de données en particulier

=> Sous SQL Server 2000

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
30
31
32
33
34
35
36
37
SELECT    D.name AS database_name
    , C.last_backup_date_time
    , CAST(C.n / 1440 AS varchar(3)) + ' jours '
      + CAST((C.n % 1440) / 60 AS varchar(2)) + ' heures '
      + CAST(C.n % 60 AS varchar(2)) + ' minutes' AS backup_taken_ago
    , C.backup_type
    , C.backup_size
    , C.compressed_backup_size
FROM    sysdatabases AS D
LEFT JOIN  (
      SELECT  database_name
        , last_backup_date_time
        , DATEDIFF(minute, last_backup_date_time, GETDATE()) AS n
        , backup_type
        , backup_size
        , compressed_backup_size
      FROM  (
          SELECT  database_name
            , MAX(backup_finish_date) AS last_backup_date_time
            , CASE type
              WHEN 'D' THEN 'Full Database'
              WHEN 'I' THEN 'Differential database'
              WHEN 'L' THEN 'Transaction Log'
              WHEN 'F' THEN 'File or filegroup'
              WHEN 'G' THEN 'Differential file'
              WHEN 'P' THEN 'Partial'
              WHEN 'Q' THEN 'Differential partial'
            END AS backup_type
            , backup_size  
            , compressed_backup_size
          FROM  msdb.dbo.backupset AS S
          GROUP  BY database_name, type, backup_size, compressed_backup_size
        ) AS BH
      ) AS C ON D.name = C.database_name
WHERE    1 = 1
AND    D.name = 'maBaseDeDonnees'
ORDER BY  D.name

=> Sous SQL Server 2005, 2008 et suivants :

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
30
31
32
33
34
35
36
37
38
39
40
;WITH
  CTE AS
  (
    SELECT  database_name
      , last_backup_date_time
      , DATEDIFF(minute, last_backup_date_time, GETDATE()) AS n
      , backup_type
      , backup_size
      , compressed_backup_size
    FROM  (
        SELECT  database_name
          , MAX(backup_finish_date) AS last_backup_date_time
          , CASE type
            WHEN 'D' THEN 'Full Database'
            WHEN 'I' THEN 'Differential database'
            WHEN 'L' THEN 'Transaction Log'
            WHEN 'F' THEN 'File or filegroup'
            WHEN 'G' THEN 'Differential file'
            WHEN 'P' THEN 'Partial'
            WHEN 'Q' THEN 'Differential partial'
          END AS backup_type
          , backup_size  
          , compressed_backup_size
        FROM  msdb.dbo.backupset AS S
        GROUP  BY database_name, type, backup_size, compressed_backup_size
      ) AS BH
  )
SELECT    D.name AS database_name
    , C.last_backup_date_time
    , CAST(C.n / 1440 AS varchar(3)) + ' jours '
      + CAST((C.n % 1440) / 60 AS varchar(2)) + ' heures '
      + CAST(C.n % 60 AS varchar(2)) + ' minutes' AS backup_taken_ago
    , C.backup_type
    , C.backup_size
    , C.compressed_backup_size
FROM    sys.databases AS D
LEFT JOIN  CTE AS C ON D.name = C.database_name
WHERE    1 = 1
AND    D.name = 'maBaseDeDonnees'
ORDER BY  D.name

Bonne gestion des sauvegardes !

@++ ;)

Laisser un commentaire