La propriété DataAndBackupOnSeparateLogicalVolumes fournie par la facette Database Maintenance permet d’établir une règle qui se base sur le postulat « Les fichiers de données d’une base ne peuvent pas résider sur le même disque que les sauvegardes qui la concernent ». Dans le cadre de l’implémentation d’un standard de configuration d’instances SQL Server l’utilisation de cette propriété peut se révéler nécessaire. Cependant pour l’avoir mis en place chez un de mes clients, j’ai pu constater que son comportement n’était pas celui que j’espérais.

En effet pour mieux comprendre le comportement que j’en attendais voici un petit exemple d’utilisation de cette propriété. La mise en place d’une règle qui vérifie cette propriété utilisera la condition suivante (pour plus d’informations sur la mise en place des règles c’est par ici)

La définition de la règle est la suivante :

Créons tout d’abord une base de données test_backup à l’aide du script suivant :

USE [master];
GO

CREATE DATABASE test_backup;
GO

Les fichiers de bases de données se trouvent sur le disque C: dans mon cas.

SELECT
type_desc,
name,
physical_name
FROM sys.database_files;

L’évaluation de la règle à ce stade pour la base de données test_backup donne un résultat positif. En effet pour le moment aucune sauvegarde n’existe.

 Premier test : création d’une sauvegarde de la base de données test_backup sur le même disque que ses fichiers de données.

BACKUP DATABASE test_backup
TO DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\test_backup.bak’
WITH INIT;

Procédons maintenant à l’évaluation de notre règle :

L’évaluation de notre règle a bien évidement échouée.

 Deuxième test : Création d’une sauvegarde de bases de données test_backup sur un disque différent que ses fichiers de données

BACKUP DATABASE test_backup
TO DISK = ‘E:\sauvegarde sql\test_backup.bak’
WITH INIT;

A ce stade nous ne sommes toujours pas en phase avec notre règle puisqu’un fichier de sauvegarde est toujours présent sur le même disque que les fichiers de données. L’évaluation nous le confirme d’ailleurs .

 

On pourrait penser qu’en déplaçant notre fichier de sauvegarde présent sur C: sur le disque E: (comme effectué lors de notre dernière sauvegarde) notre règle serait respectée mais il n’en est rien. Vous pouvez effectuer le test et vous verrez que l’évaluation de la règle correspondante sera toujours en échec. En réalité celle-ci, par le biais de la propriété DataAndBackupOnSeparateLogicalVolumes, ne se base pas sur l’emplacement physique d’une sauvegarde mais sur l’historique des sauvegardes d’une base de données.

SELECT
BS.backup_set_id,
BMS.media_set_id,
BS.backup_start_date,
BS.backup_finish_date,
BS.type,
BS.database_name,
BMF.physical_device_name
FROM msdb.dbo.backupset AS BS
JOIN msdb.dbo.backupmediaset BMS
ON BS.media_set_id = BMS.media_set_id
JOIN msdb.dbo.backupmediafamily AS BMF
ON BMF.media_set_id = BMS.media_set_id
WHERE BS.database_name = ‘test_backup’
ORDER BY backup_finish_date DESC;

qui donne le résultat suivant

Amusons-nous et modifions le comportement de la procédure stockée système sp_delete_backuphistory située dans la base de données msdb en donnant pour paramètre @backup_set_id et @media_set_id pour supprimer de façon granulaire les lignes d’historiques associés à une base de données. La procédure stockée d’origine prend en paramètre une date et peut donc supprimer potentiellement un ensemble d’historique de plusieurs bases de données. Ceci est bien sûr valable qu’à titre de test et ne doit pas être reproduit en production

USE msdb;
GO

CREATE PROCEDURE sp_delete_backuphistory_dbn
@backup_set_id_p int,
@media_set_id_p int
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @backup_set_id TABLE (backup_set_id INT)
    DECLARE @media_set_id TABLE (media_set_id INT)
    DECLARE @restore_history_id TABLE (restore_history_id INT)
    INSERT INTO @backup_set_id (backup_set_id)
    SELECT DISTINCT backup_set_id
    FROM msdb.dbo.backupset
    WHERE backup_set_id = @backup_set_id_p
    INSERT INTO @media_set_id (media_set_id)
    SELECT DISTINCT media_set_id
    FROM msdb.dbo.backupset
    WHERE media_set_id = @media_set_id_p
    INSERT INTO @restore_history_id (restore_history_id)
    SELECT DISTINCT restore_history_id
    FROM msdb.dbo.restorehistory
    WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
    BEGIN TRANSACTION
     DELETE FROM msdb.dbo.backupfile
     WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
     IF (@@error >  0)
      GOTO Quit
     DELETE FROM msdb.dbo.backupfilegroup
     WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
     IF (@@error > 0)
      GOTO Quit
     DELETE FROM msdb.dbo.restorefile
     WHERE restore_history_id IN (SELECT restore_history_id FROM @restore_history_id)
     IF (@@error > 0)
      GOTO Quit
     DELETE FROM msdb.dbo.restorefilegroup
     WHERE restore_history_id IN (SELECT restore_history_id FROM @restore_history_id)
     IF (@@error > 0)
      GOTO Quit
     DELETE FROM msdb.dbo.restorehistory
     WHERE restore_history_id IN (SELECT restore_history_id FROM @restore_history_id)
     IF (@@error  0)
      GOTO Quit
     DELETE FROM msdb.dbo.backupset
     WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
     IF (@@error > 0)
      GOTO Quit
     DELETE msdb.dbo.backupmediafamily
     FROM msdb.dbo.backupmediafamily bmf
     WHERE bmf.media_set_id IN (SELECT media_set_id FROM @media_set_id)
      AND ((SELECT COUNT(*) FROM msdb.dbo.backupset
            WHERE media_set_id = bmf.media_set_id) = 0)
     IF (@@error > 0)
      GOTO Quit
     DELETE msdb.dbo.backupmediaset
     FROM msdb.dbo.backupmediaset bms
     WHERE bms.media_set_id IN (SELECT media_set_id FROM @media_set_id)
      AND ((SELECT COUNT(*) FROM msdb.dbo.backupset
            WHERE media_set_id = bms.media_set_id) = 0)
     IF (@@error > 0)
      GOTO Quit
    COMMIT TRANSACTION
    RETURN
    Quit:
    ROLLBACK TRANSACTION
END

On peut maintenant supprimer les lignes d’historiques de sauvegarde de la base de données test_backup sur C:

EXEC sp_delete_backuphistory_dbn 274, 143;

On peut maintenant voir si le résultat de notre évaluation est correct.

Ok donc tout cela pour dire que la propriété DataAndBackupOnSeparateLogicalVolumes ne se réfère pas à l’emplacement physique d’une sauvegarde à un instant T mais sur l’historique des sauvegardes d’une base de données stockée dans msdb. Il y a quelques cas où cela risque d’être problématique à mon avis :

• Une sauvegarde à la volée en mode copy_only est effectuée sur le même disque que les fichiers d’une base pour une raison quelconque et ne vient pas perturber le plan de maintenance des sauvegardes en place. Ce type de sauvegarde n’est en général pas stockée de façon permanente sur le système de fichiers du serveur SQL et ne doit pas être pris en compte dans l’évaluation de la règle.
• Un changement au niveau du stockage (la lettre dédiée aux fichiers de sauvegardes devient du coup la même que celle qui était réservée initialement aux fichiers de données d’une base). Dans ce cas l’ensemble de l’historique devient caduque pour la vérification de l’évaluation.
• L’ensemble de l’historique des sauvegardes n’est pas forcément à prendre en compte d’autant plus que la plupart du temps celles-ci sont archivés au fur et à mesure sur un support annexe.

Une solution serait de supprimer l’historique des sauvegardes pour chaque base de données au fur et à mesure mais personnellement j’aime garder ces historiques assez longtemps à des fins de statistiques de volumétrie par exemple. Une autre solution est d’implémenter son propre script SQL de vérification qui sera utilisée à l’aide de la fonction ExecuteSQL(). Ce script permet de :

• ne pas tenir en compte les sauvegardes de type copy_only
• de ne prendre en compte que le jeu de sauvegarde depuis la dernière sauvegarde complète (dans mon cas le reste des sauvegardes est archivé via un outil tiers)
• vérifier qu’une sauvegarde ne soit pas sur le même lecteur que les fichiers de données et des journaux de transactions.

A noter le forçage d’une collation lors de la comparaison des lecteurs de fichiers et de sauvegardes en cas de collation différente pour une base de données concernée.

Bien entendu libre à vous d’implémenter vos propres règles en fonction de votre besoin

ExecuteSql(‘numeric’, ‘SELECT
ISNULL(SUM(CASE WHEN SUBSTRING(BMF.physical_device_name, 1, CASE CHARINDEX( »\ », BMF.physical_device_name) WHEN 1 THEN 1 ELSE CHARINDEX( »\ », BMF.physical_device_name) – 2 END) <> SUBSTRING(DF.physical_name, 1, CHARINDEX( »\ », DF.physical_name) – 2) COLLATE French_CI_AS THEN 0 ELSE 1 END), 0) AS result
FROM msdb.dbo.backupset AS BS
JOIN msdb.dbo.backupmediaset BMS
ON BS.media_set_id = BMS.media_set_id
JOIN msdb.dbo.backupmediafamily AS BMF
ON BMF.media_set_id = BMS.media_set_id
CROSS JOIN sys.database_files AS DF
WHERE BS.database_name = DB_NAME(DB_ID())
AND BS.is_copy_only = 0
  AND BS.backup_start_date = (SELECT MAX(BS.backup_start_date)
                               FROM msdb.dbo.backupset AS BS
                               JOIN msdb.dbo.backupmediaset BMS
                                ON BS.media_set_id = BMS.media_set_id
                               WHERE BS.database_name = DB_NAME(DB_ID())
                                AND BS.type =  »D »
                                 AND BS.is_copy_only = 0);’)

Il suffit d’implémenter une règle comme ci-dessous et le tour est joué.

Bonne mise en place de règles !!

David BARBARIN (Mikedavem)
MVP SQL Server