SQL Server vu par Christian Robert

SQL Server : Restaurer une base de données à la milliseconde près (RESTORE LOG WITH STOPAT)

Beaucoup de DBA débutant passe à côté d'une fonctionnalité majeure de SQL Server, la possibilité de restaurer une base de données à n'importe quel moment dans le temps. En effet SQL Server ne réalise pas une sauvegarde de ses données comme de simples fichiers.

Le processus de sauvegarde utilise en parallèle le ou les fichiers de données (MDF / NDF) et le journal de transaction. Le journal de transaction contient toutes les « requêtes » qui ont modifiées les données (d'où le nom de journal et la référence aux transactions qui modifient les données), ainsi que leur ordre et les date et heures d'exécution. Comme celles-ci sont toutes enregistrées il devient possible de les ré exécuter.

Pour faire ce type de restauration à un point précis dans le temps il faut dès lors faire des sauvegardes régulières du journal de transaction en plus des sauvegardes complètes et que la base de données soit en mode de récupération « complet » ou « bulk logged ».

La restauration quant à elle fera appel à la dernière sauvegarde complète de la base de données et à la succession des sauvegardes du journal de transactions (elles sont généralement fréquentes, de 5min à quelques heures).

Mettons que nous ayons l'historique suivant de sauvegarde de notre base de données (Stratégie de type 1 complète par jour et la sauvegarde des journaux toutes les heures) :

• Sauvegarde Complète : Lundi 8h00
• Sauvegarde du journal de Transactions : Lundi 9h00
• Sauvegarde du journal de Transactions : Lundi 10h00
• Sauvegarde du journal de Transactions : Lundi 11h00
• Sauvegarde du journal de Transactions : Lundi 12h00

Maintenant j'ai un coup de téléphone de mon patron, indiquant qu'il a perdu (volontairement ou non) le dossier d'un de ses clients vers 10h45.

Premièrement je remets la main sur mes jeux de sauvegarde, dans mon exemple j'aurais besoin de la dernière sauvegarde complète (ma première ligne) et des transactions exécutées jusqu'à 10h45 (donc des 3 suivantes aussi).

Deuxièmement, je sauvegarde ma base de données actuelle (ou je restaure à côté dans une nouvelle base de données). C'est important car le risque de se tromper (l'erreur est humaine) est non négligeable (mauvais jeu de sauvegarde, etc.). On réalise alors ce qu'on appelle la sauvegarde de la queue du journal de transaction (TAIL LOG).

BACKUP LOG MaBase
TO DISK = 'C:\backup\Mabase_TailLog.TRN'
WITH NO_TRUNCATE

Cette sauvegarde permettra aussi de ne pas utiliser l'option REPLACE lorsque nous écraserons la base de données actuelle. TRN est l'extension généralement utilisée pour les sauvegardes de journaux de transactions.

Troisièmement on restaure la sauvegarde complète.

RESTORE DATABASE MaBase
FROM DISK = 'C:\backup\Mabase_Lun_0800.BAK'
WITH NORECOVERY

Notez le WITH NORECOVERY qui signale que l'on va restaurer d'autres sauvegardes. Je n'ai pas mis REPLACE, car j'ai sauvegardé la queue du journal avant d'effectuer le RESTORE, si vous ne le faites pas vous aurez une erreur lors du RESTORE.

Quatrièmement on restaure les sauvegardes du journal de transactions (sauf la dernière)

-- Journaux de 9h00
RESTORE LOG MaBase
FROM DISK = 'C:\backup\Mabase_Lun_0900.TRN'
WITH NORECOVERY

-- Journaux de 10h00
RESTORE LOG MaBase
FROM DISK = 'C:\backup\Mabase_Lun_1000.TRN'
WITH NORECOVERY

Toujours NORECOVERY tant que l'on a d'autres sauvegardes à restaurer pour cette base.

Cinquièmement on restaure la dernière sauvegarde du journal de transaction

RESTORE LOG MaBase
FROM DISK = 'C:\backup\Mabase_Lun_1100.TRN'
WITH RECOVERY, STOPAT = '20071001 10:44:00'

On spécifie RECOVERY car il s'agit de la dernière sauvegarde à restaurer, et l'on indique la date et l'heure dans la clause STOPAT. J'ai volontairement choisis de restaurer à 10h44 et non pas 10h45 par précaution, il est assez difficile d'estimer la date et heure réelle de restauration à utiliser, c'est généralement du tâtonnement avec 3 à 4 restauration qui vous permettra de tomber juste.

Il est possible d'utiliser 2 autres options avec le STOPAT :

• Marque : On indique une marque (qui porte un nom) dans le journal, il ne reste plus alors qu'à restaurer jusqu'à ce nom ou avant ce nom
• LSN : on indique le numéro de la transaction jusqu'à laquelle restaurer (nécessite de savoir lire le journal de transaction)

Prenons le cas la marque, on marque le journal de cette manière :

BEGIN TRANSACTION XYZ WITH MARK 'Nom_De_La_Marque'

XYZ est le nom de la transaction, elle ne sous importe pas. Le nom de la marque est spécifié dans la chaîne de caractère derrière le WITH MARK. Au niveau de la restauration on spécifiera dans la clause WITH l'une des options suivantes :

STOPATMARK = 'Nom_De_La_Marque'

STOPBEFOREMARK = 'Nom_De_La_Marque'

La première restore la transaction marquée, la seconde d'arrête juste avant et ne l'exécute pas.

La marque dans le journal est idéale pour indiquer que l'on commence une opération critique (une mise en production par exemple). On peut même les ajouter automatiquement via l'application.

Au final, on voit l'intérêt numéro 1 de la sauvegarde et de la gestion des journaux de transactions par SQL Server. Sans eux la seule option de restauration disponible est la restauration de données qui induira forcément une perte de données. Le journal permet de récréer la base de données n'importe quand et de répondre à tout type de demande de restauration.

Bonne restauration…