Le passage du mode de récupération de FULL à SIMPLE est une méthode souvent utilisée lorsqu’une mise à jour majeure d’une application est effectuée. Des scripts SQL sont lancés lors des ces mises à jour sur la base de données et le mode de récupération SIMPLE permet de réaliser deux choses : contrôler plus ou moins l’accroissement du journal des transactions et augmenter ou du moins ne pas détériorer le temps d’exécution des scripts SQL (le mode de récupération SIMPLE permet une journalisation moindre par rapport au mode de récupération FULL). Mais le fait de passer en mode SIMPLE rompt la chaîne de séquence des LSN ce qui signifie que les sauvegardes du journal ne pourront plus se faire correctement. Pour pouvoir reprendre cette séquence correctement le passage en mode de récupération FULL suivie d’une sauvegarde est obligatoire.  La sauvegarde la plus communément utilisée que j’ai pu voir est une sauvegarde complète. Mais doit on réellement effectuer ce type de sauvegarde pour reprendre cette chaîne de séquence ?

La réponse est bien évidement NON. Une sauvegarde différentielle suffit amplement pour restaurer la chaîne séquentielle des LSN du journal des transactions.

Commençons par créer une base de données quelconque (database_test) :

CREATE DATABASE database_test;
GO

USE database_test;
GO

Vérifions son mode de récupération et le cas échéant initialisons le à FULL :

IF (SELECT
        recovery_model_desc
    FROM sys.databases
    WHERE [name] = ‘database_test’) <> ‘FULL’
BEGIN
PRINT ‘Changement mode de récupération';
ALTER DATABASE database_test SET RECOVERY FULL;
END;
GO

Effectuons une sauvegarde complète de la base de données. En effet sans ce point de départ aucune séquence du journal n’est maintenue par SQL Server.

BACKUP DATABASE database_test TO DISK = ‘E:\MSSQL\B\BACKUP\DATABASE_TEST_FULL.BAK';
GO

Créons une table et insérons quelques données.

CREATE TABLE dbo.test
(
ID INT IDENTITY PRIMARY KEY,
TEXTE VARCHAR(50) NOT NULL
);
GO

INSERT INTO dbo.test (TEXTE) VALUES (‘TEXTE’);
GO 1000

Effectuons une première sauvegarde du journal.

BACKUP LOG database_test TO DISK = ‘E:\MSSQL\B\BACKUP\DATABASE_TEST_1.TRN';
GO

Passons maintenant en mode de récupération SIMPLE.

РPassage en mode de r̩cup̩ration SIMPLE
ALTER DATABASE database_test SET RECOVERY SIMPLE;
GO

Insérons un jeu complémentaire de données dans la table dbo.test.

INSERT INTO dbo.test (TEXTE) VALUES (‘TEXTE’);
GO 1000

Passons de nouveau en mode FULL.

ALTER DATABASE database_test SET RECOVERY FULL;
GO

A ce moment précis la sauvegarde du journal des transactions renverra une erreur car la chaîne de séquence des transactions a été rompu par le passage en mode de récupération SIMPLE.

Effectuons une sauvegarde différentielle.

BACKUP DATABASE database_test TO DISK = ‘E:\MSSQL\B\BACKUP\DATABASE_TEST_DIFF.BAK’
WITH DIFFERENTIAL;
GO

. et effectuons à nouveau une sauvegarde du journal.

BACKUP LOG database_test TO DISK = ‘E:\MSSQL\B\BACKUP\DATABASE_TEST_2.TRN';
GO

La sauvegarde ne génère plus d’erreur. La chaîne de séquence des transactions est de nouveau maintenue par SQL Server. La raison en est ici évidente : La sauvegarde du journal se base maintenant sur le LSN enregistrée lors de la sauvegarde différentielle. Pour s’en rendre compte on peut utiliser la commande RESTORE HEADERONLY qui nous informe sur les LSN de début et de fin enregistrées lors des sauvegardes.

Les informations concernant les LSN des sauvegardes complètes, différentielles et du dernier journal sont :

RESTORE HEADERONLY FROM DISK = ‘E:\MSSQL\B\BACKUP\DATABASE_TEST_FULL.BAK';
GO
– FirstLSN                  LastLSN                   DatabaseBackupLSN
– 19000000025200099 19000000029500001 0

RESTORE HEADERONLY FROM DISK = ‘E:\MSSQL\B\BACKUP\DATABASE_TEST_DIFF.BAK';
GO
– FirstLSN                  LastLSN                   DatabaseBackupLSN
– 23000000034700006 23000000035100001 19000000025200099

RESTORE HEADERONLY FROM DISK = ‘E:\MSSQL\B\BACKUP\DATABASE_TEST_2.TRN';
GO
– FirstLSN                  LastLSN                   DatabaseBackupLSN
– 23000000035100001 23000000034700006 19000000025200099

On constate de suite que le premier LSN enregistré pour la sauvegarde du journal (après le passage du mode SIMPLE en FULL) a la valeur « 23000000035100001 ». Cette valeur correspond au dernier LSN (LastLSN) enregistré lors de la sauvegarde différentielle. La séquence est donc bien respectée. C’est donc la sauvegarde différentielle qui devient le point de référence des nouvelles sauvegardes du journal !!

PS : On remarque également que chaque sauvegarde (de journaux ou différentielle) référence le premier LSN enregistré pour la sauvegarde complète (LSN = 19000000025200099).

Voilà pour ce qui est à mon avis un des nombreux mythes du monde DBA !!! Bonne sauvegarde (et restauration)

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