Je vois de temps en temps des processus de sauvegarde de bases de données en entreprise qui arrêtent le serveur SQL pour sauvegarder les fichiers de bases de données (fichiers de données et des journaux de transactions). Cependant cette méthode est à proscrire. Dans ce billet j’expliquerais quelles en sont les raisons.

Il existe au moins deux cas où cette méthode peut poser problème :

- Un administrateur de bases de données veut restaurer une base de données sur un de ces serveurs mais le fichier du journal des transactions est trop volumineux. Dans ce cas il suffit de rattacher le ou les fichiers de bases de données en demandant la reconstruction du journal.
- L’administrateur de bases de données veut restaurer une base et on ne lui fournit que le fichier de données (en général le fichier avec l’extension mdf). Là encore, il faut rattacher les fichiers de bases de données en demandant la reconstruction du journal.

Pour bien comprendre de quoi il s’agit un exemple vaut mieux qu’une longue explication :

Commençons par créer une base de données.

CREATE DATABASE MyDB
ON
( NAME = Sales_dat,
    FILENAME = ‘C:\DATABASES\MyDB.mdf’
)
LOG ON
( NAME = Sales_log,
    FILENAME = ‘C:\DATABASES\MyDB_Log.ldf’,
    SIZE = 500MB
);
GO

Ensuite créons une table.

USE MyDB;
GO

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

Dans une transaction nous insérons un jeu de données dans une table. Cette transaction reste ouverte.

BEGIN TRANSACTION T1;

INSERT INTO dbo.test VALUES (1, ‘TEST1′);
INSERT INTO dbo.test VALUES (1, ‘TEST2′);

Un CHECKPOINT est exécuté à ce moment. Celui-ci écrit donc les pages de données modifiées et les enregistrements correspondant à la transaction sur disque.

Le processus de sauvegarde est lancé. Celui-ci arrête donc le serveur par script.

> net stop mssqlserver

Les fichiers sont ensuite sauvegardés. (Dans mon cas je n’ai qu’une instance alors je copierais les fichiers sous un autre nom et je supprimerais la base de données MyDB par la suite).

Sur autre instance nous voulons restaurer la base de données MyDB en rattachant simplement le fichier de données (MyDB_new.mdf). Nous sommes dans l’un des deux cas décrits précédemment.

CREATE DATABASE MyDB
ON (FILENAME = ‘C:\DATABASES\MyDB_new.mdf’)
FOR ATTACH ;
GO

et voici le message d’erreur rencontré :

File activation failure. The physical file name « C:\DATABASES\MyDB_Log.ldf » may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘MyDB’. CREATE DATABASE is aborted.

Pourquoi n’est il pas possible de restaurer le fichier des données ? Le message d’erreur le dit clairement : la base de données a été arrêté alors qu’il existait encore une transaction ouverte.

Lorsqu’une base de données est attachée, une session de récupération est également exécutée (pour plus de détail voir ce billet). Dans notre cas le fichier de données contient des pages de données (via CHECKPOINT) d’une transaction encore ouverte. De plus le fichier journal étant absent, il est impossible d’exécuter les phases nécessaires à l’annulation de cette transaction !! Le journal des transactions permet d’exécuter les phases REDO et UNDO d’une session de récupération. C’est la raison pour laquelle un arrêt du serveur pour sauvegarder les fichiers est à mon avis risqué. Il est préférable de détacher proprement la base de données à l’aide de l’instruction sp_detach_db car cette procédure garantit qu’une base soit fermée correctement. En cas d’indisponibilité du fichier journal, il sera quand possible de la rattacher.

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