Une de mes tâches du moment est de pouvoir justifier ou non d’une éventuelle extension de disques  sur le SAN en fonction de l’évolution du volume des données des bases. Cela peut se faire de plusieurs façons mais une méthode simple est de se baser sur l’historique des sauvegardes complètes des bases qui sont vraiment représentatives des données réelles qu’elles hébergent.

Pour cela il suffit d’interroger les tables systèmes backupfile ( qui fournit des informations sur les fichiers ou journaux des bases de données) et backupset ( qui fournit des informations sur l’historique des jeux de sauvegardes) dans la base de données msdb.

Le script suivant se base sur ces tables systèmes et proposent un suivi par mois et par année de la volumétrie des sauvegardes qui permettra de dresser un bilan d’évolution des données et donc en déduire un prévisionnel d’extension de place disques si besoin.

DECLARE @database_name SYSNAME;
DECLARE @previousNbYear INT;

– @database_name
– Nom de la base ou NULL pour toutes les bases
SET @database_name = NULL;

– @previousNbYear
– Nombre d’années d’historiques à visualer (>=0)
SET @previousNbYear = 0;

WITH CTE_BACKUP_YEAR_MONTH_BACKUP_SET_ID
AS
(   
    SELECT
        bs.database_name,
        bs.backup_set_id,
        bs.type AS type_backup,
        MONTH(backup_start_date) AS backup_month,
        YEAR(backup_start_date) AS backup_year,
        bf.backup_size / 1024 / 1024 AS backup_size_Mo
    FROM msdb.dbo.backupfile bf
    INNER JOIN msdb.dbo.backupset bs
    ON bf.backup_set_id = bs.backup_set_id
    WHERE (database_name = @database_name OR @database_name IS NULL)
      AND YEAR(bs.backup_start_date) >= DATEADD(YY,-1 * @previousNbYear,YEAR(GETDATE()))
       AND bs.type = ‘D’
),
CTE_SUM_BACKUP_SIZE_AVG_DURATION
AS
(
    SELECT
        database_name,
        type_backup,
        backup_month,
        backup_year,
        SUM(backup_size_Mo) AS backup_size_Mo
    FROM CTE_BACKUP_YEAR_MONTH_BACKUP_SET_ID
    GROUP BY database_name,backup_set_id, type_backup, backup_year, backup_month
),
CTE_SUM_BACKUP_SIZE_AVG_DURATION_PER_MONTH
AS
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY database_name, type_backup, backup_year DESC, backup_month DESC) AS num,
        database_name,
        type_backup,
        backup_year,
        backup_month,
        CAST(AVG(backup_size_Mo) AS DECIMAL(10,2)) AS avg_backup_size_Mo
    FROM CTE_SUM_BACKUP_SIZE_AVG_DURATION
    GROUP BY database_name, type_backup, backup_year, backup_month
),
CTE_SUMMARIZE_DIFFERENCE
AS
(
    SELECT
        c1.database_name,
        CASE c1.type_backup
            WHEN ‘D’ THEN ‘1 – FULL’
            WHEN ‘I’ THEN ‘2 – DIFFERENTIAL’
            WHEN ‘L’ THEN ‘3 – JOURNAL’
            WHEN ‘F’ THEN ‘4 – FILEGROUP FULL’
            WHEN ‘G’ THEN ‘5 – FILEGROUP DIFFERENTIAL’
            WHEN ‘P’ THEN ‘6 – PARTIAL’
            ELSE ‘7 – PARTIAL DIFFERENTIAL’
        END AS type_backup,
        c1.backup_year,
        c1.backup_month,
        c1.avg_backup_size_Mo,
        c2.backup_month AS backup_month_previous,
        c2.avg_backup_size_Mo AS avg_backup_size_previous_Mo,
        c1.avg_backup_size_Mo – c2.avg_backup_size_Mo AS size_difference_backup_Mo,
        CAST(CAST((c1.avg_backup_size_Mo – c2.avg_backup_size_Mo) / c2.avg_backup_size_Mo * 100.00 AS DECIMAL(10,2)) AS VARCHAR(10))
         + ‘%’ AS difference_size_ratio
    FROM CTE_SUM_BACKUP_SIZE_AVG_DURATION_PER_MONTH c1
    INNER JOIN CTE_SUM_BACKUP_SIZE_AVG_DURATION_PER_MONTH c2
    ON c1.num = c2.num – 1
     AND c1.database_name = c2.database_name
    WHERE c1.avg_backup_size_Mo – c2.avg_backup_size_Mo >= 0 — On supprime les backups qui n’ont pas évolués.
)
SELECT
    database_name,
    type_backup,   
    backup_year,
    backup_month,
    avg_backup_size_Mo,
    backup_month_previous,
    avg_backup_size_previous_Mo,
    size_difference_backup_Mo,
    difference_size_ratio
FROM CTE_SUMMARIZE_DIFFERENCE
UNION ALL
SELECT
    database_name,
    type_backup,   
    backup_year,   
    NULL,
    MAX(avg_backup_size_Mo),
    NULL,
    NULL,
    SUM(size_difference_backup_Mo),
    CAST(CAST(SUM(size_difference_backup_Mo) / MAX(avg_backup_size_Mo) * 100.00 AS DECIMAL(10,2)) AS VARCHAR(10))
     + ‘%’
FROM CTE_SUMMARIZE_DIFFERENCE
GROUP BY database_name, type_backup, backup_year
ORDER BY database_name, type_backup, backup_year DESC, backup_month DESC;

et un extrait de résultat :

Bon audit de volumétrie !!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon