Retourner votre base de données SQL Server à un point précis dans le temps

Introduction

Il y a certaines circonstances dans lesquelles vous pouvez avoir besoin de restaurer votre base de données à un point précis dans le temps (point in time recovery). Ces raisons sont généralement liées à l’exécution d’instructions DML ou DDL accidentelles. Par exemple:

• Quelqu’un a exécuté une mise à jour sans clause WHERE
• Quelqu’un a effacé accidentellement une table
• Votre mise à jour de base de données a échouée, et a laissée la base de données dans un état inconnu et/ou instable

SQL Server fournit deux mécanismes pour vous protéger contre ces événements:

• Snapshots de base de données SQL Server
• Restauration point-in-time

SQL Server Database snapshots

Si vous savez que vous allez effectuer une tâche qui peut endommager vos données, il serait prudent de prendre un snapshots de votre base de données avant de le faire.

Les snapshots de base de données sont rapides et faciles à créer, et très facile à récupérer. Bien sûr, si vous ne utilisez pas de snapshots de base de données dans le cadre de votre stratégie de protection des données cela n’est peut être pas une option pour vous, et vous serez obligé d’utiliser le point-à-temps méthode de restauration . Pour en savoir plus à ce sujet, veuillez consulter la documentation online.

Restauration point-in-time

Il est important de savoir que revenir à un point précis dans le temps n’est pas toujours une option, car cela dépend de 2 choses:

1. Le mode de récupération (recovery model) de votre base de données et
2. Votre chaîne de transaction log

Mode de récupération de serveur SQL

SQL Server offre 3 modes de récupération

• Simple
• Bulk-connected
• Complet

Le choix de votre mode de récupération est une décision cruciale, car il définit essentiellement la quantité de données que vous êtes prêt à perdre en cas de catastrophe.

Dans le cas d’un mode de récupération simple, vous êtes acceptez essentiellement qu’il est acceptable de perdre toutes les modifications apportées à votre base de données depuis la dernière sauvegarde complète. L’avantage de ce modèle de récupération est bien sûr que cela demande très peu d’administration. Vous ne devez pas vous soucier de quoi que ce soit en tant que chaîne de transaction log ou de tonnes de sauvegardes de logs, ou même lorsque le journal sera tronqué. Évidemment, cela n’est pas acceptable vos bases de données critiques, mais dans certains cas c’est acceptable et même recommandable.

Le but du mode de récupération bulk-connected est de vous permettre d’effectuer des opérations bulk sans écrire chaque transaction dans le transaction log, et de ce fait d’améliorer la performance de votre opération bulk. L’inconvénient étant bien sûr que chaque transaction n’est pas enregistrée dans le log, et cela ne vous permet pas de faire une restauration point-in-time.

Afin d’être en mesure de restaurer à un point précis dans le temps, vous avez besoin d’avoir votre base de données en mode de récupération complète (Full). Cela signifie que chaque événement qui se déroule dans la base de données est écrit dans le log, c’est pourquoi il est alors possible pour vous de restaurer jusqu’à un point spécifique. Mais avoir seulement la base de données en mode de récupération complète ne suffit pas. Il vous faut aussi avoir une chaîne de transaction log complète, pour pouvoir être en mesure de restaurer votre base de données au moment dont vous avez besoin.

La chaîne de transaction logs

La chaîne commence lorsque vous créez une sauvegarde d’une base de données qui est en mode de récupération complète. Cela signifie que, entre cette sauvegarde complète et la prochaine pleine sauvegarder chaque opération qui a eu lieu dans la base de données sera enregistrée soit dans la sauvegarde complète, dans la sauvegarde différentielle ou une sauvegarde du transaction log.

Cela garantit que lorsque vous avez besoin de restaurer votre base de données à un moment précis ou à une transaction spécifique, toutes les entrées de journaux requises seront là pour rendre cela possible.

Tant que la chaîne de transaction log n’est pas brisée, par exemple en changeant le mode de récupération ou le retour à un snapshot de base de données, vous serez en mesure de récupérer votre base de données à un point précis dans le temps. De plus, faire une sauvegarde complète entre les sauvegardes de transaction logs ne brise pas la chaîne de transaction logs.

Si votre chaîne de journal est brisée, vous pouvez la recréer en créant simplement une nouvelle sauvegarde complète ou différentielle.

Exécution d’une restauration point-in-time avec SQL Server

SQL Server vous permet d’effectuer une restauration point-in-time en utilisant T-SQL ou SSMS.

Pour être en mesure d’effectuer une une restauration point-in-time vous devez restaurer la sauvegarde complète qui a pris fin avant le point que vous souhaitez restaurer, puis restaurer toutes les sauvegardes des transaction logs, y compris celle qui contient le moment que vous voulez restaurer.

Avec SQL Server Management Studio

Pour utiliser SQL Server Management Studio, vous devez suivre la procédure suivante:

1. Faites un clic droit sur la base de données que vous souhaitez revenir à un point dans le temps

2. Sélectionnez Tasks/Restore/Database

   

   SSMS va vérifier automatiquement toutes les sauvegardes disponibles à partir de la dernière sauvegarde complète

3. Dans le dialogue restore database, sélectionnez l’option Timeline

4. Entrez le moment précis où vous désirez restaurer la base de données en faisant glisser le curseur à l’endroit voulu, ou en sélectionnant à l’aide de la date et l’heure

   

   Notez que pour SQL Server 2012, une sauvegarde de la fin du transaction log sera créée automatiquement avant de commencer la restauration.

5. Cliquez sur OK
6. Cliquez sur OK à nouveau

Cela va démarrer la restauration.

Utilisation de Transact SQL

Une restauration point-in-time nécessite une chaîne de transaction log complète, quelle que soit la méthode utilisée pour effectuer la restauration. Cela signifie que vous devez restaurer la dernière sauvegarde complète et toutes les sauvegardes de transaction logs, y compris celle qui contient le moment que vous souhaitez restaurer.

La sauvegarde point-in-time est toujours restaurée à partir d’une sauvegarde du transaction log. Ce qui signifie que vous devez utiliser l’instruction RESTORE LOG, avec la clause STOP AT pour le point-in-time. Tout sera alors rétabli jusqu’à la dernière transaction qui avait été commis avec succès avant le point spécifié.

Voici un exemple de script

1. USE master
   
   RESTORE DATABASE AdventureWorks2012
   FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak'
   WITH FILE = 3
   	,REPLACE
   	,NORECOVERY;
   

   Dans l’exemple ci-dessus le backup complet est restauré depuis le fichier ‘D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak’.
   Puisque ce fichier contient de multiples backup, il faut specifier quel fichier (FILE) utiliser

   Pour identifier le numéro du fichier à utiliser, regardez la colonne Position lors de l’éxécution de cette commande:

   RESTORE HEADERONLY
   FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak'
   

   L’option REPLACE indique que la restauration devra écraser la base de données existante, de la même manière qu’en utilisant SSMS.

2. Ensuite restaurer chaque log en spécifiant la data et l’heure a laqulle s’arrêter (STOP AT). Il faut specifier la date et l’heure dans chaque commande RESTORE LOG

   RESTORE LOG AdventureWorks2012
   FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak'
   WITH FILE = 4 ,NORECOVERY ,STOPAT = '2013-09-28 10:16:28.873’;
   
   RESTORE LOG AdventureWorks2012
   FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak'
   WITH FILE = 5 ,NORECOVERY ,STOPAT = '2013-09-28 10:16:28.873’;
   

3. Enfin, restaurer la base de données

   RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;
   

La restauration point-in-time avec SQL Server, peut prendre une quantité importante de temps, en fonction de la taille de votre base de données. SQL Server fait essentiellement une restauration complète à partir de la dernière sauvegarde complète et toutes les sauvegardes de transaction log jusqu’au moment voulu.

Mais à part le temps nécessaire pour effectuer la restauration, un autre inconvénient est que la base de données n’est pas accessible durant cette période.

Si vous avez une très grande base de données, et vous souhaitez simplement annuler les dernières transactions, il est plus facile d’utiliser un outil comme Apex SQL Log pour identifier ces dernières opérations pour ensuite les annuler.

Utiliser ApexSQL Log ou ApexSQL Recover pour revenir à un point précis dans le temps

ApexSQL Log est un lecteur des transaction logs SQL Server, qui peut être utilisé pour identifier les dernières transactions effectuées par la lecture des transaction logs. ApexSQL Log peut ensuite créer un script pour vous aider à annuler ces changements.

ApexSQL Recover est un outil de récupération de base de données SQL Server qui lit les informations des fichiers ldf et mdf et permet de récupérer des opérations de suppression, de tronquage et de suppression, d’annulation de fonctionnement, d’extraction directement depuis la sauvegarde, etc.

Ces deux outils partagent la fonctionnalité pour lire les fichiers journaux des transactions et revenir à un moment spécifique.

Voici comment faire:

1. Ouvrir L’application et démarrer une nouvelle session pointant vers la base de données que vous souhaitez restaurer à un point spécifique dans le temps et cliquez sur le bouton Next

   

2. Ensuite il faut ajouter tous les backups ou les backups du transaction logs qui contiennent les instructions survenues après le moment que vous désirez restaurer.

   

3. Pour le plaisir de cette démo, nous allons choisir d’ouvrir les résultats en mode grille. Cela nous permettra de voir et de choisir les transactions que nous voulons annuler. Ceci est particulièrement utile lorsque vous n’êtes pas sûr à 100% à quel point vous devez revenir en arrière

   

4. Filtrer les opérations du log en fonction du temps. Dans ce cas, puisque vous voulez annuler les transactions qui ont eu lieu après un certain moment, il vous suffit de spécifier le “from date”, qui est le moment auquel vous voulez que la base données soit restauré

   

5. Passez en revue les opérations affichées dans la grille pour effectuer une vérification finale. Vous pouvez indiquer quelles opérations à inclure soit en soulignant soit en les sélectionnant. Dans ce cas, nous voulons faire reculer tout jusqu’à la date choisie

6. Cliquez sur le bouton Select All dans le ruban principal, ou faites un clic droit sur les résultats et choisissez l’option Select All dans le menu contextuel

   

7. Une fois toutes les opérations sélectionnées, sélectionnez l’option Undo dans le ruban principal

   

   Le script sera créé pour vous dans un éditeur de script SQL Server, ce qui vous permettra d’exécuter le script immédiatement ou de sauvegarder le script et de l’exécuter à partir de SSMS ou d’un autre outil similaire plus tard.

   

Les avantages de cette approche sont les suivants:

1. La base de données peut rester online pendant les
2. ApexSQL Log et ApexSQL Recover contient la logique necessaire pour éliminer les operations redondantes. Chaque operation n’est appliquée directement à la base de données mais plutôt traitées et seulement le script final est appliqué.
3. L’annulation des opérations de cette façon, est beaucoup plus rapide que d’avoir à restaurer l’intégralité de la base de données.

Aujourd’hui, où le temps est précieux, il est important d’identifier ce qui doit être fait, et ensuite utiliser le meilleur outil pour le faire. Si la moitié de votre base de données est corrompues, il vaudrait mieux faire une restauration complète à partir d’une sauvegarde. Si vous avez identifié seulement quelques opérations à annulér, ou si vous devez annuler seulement un jour ou deux, ApexSQL Log et ApexSQL Recover ae certainement l’ouitil à choisir pour économiser du temps et de l’argent.

Téléchargements

Veuillez télécharger les scripts associés à cet article sur notre dépôt GitHub.

S’il vous plaît nous contacter pour tous les problèmes ou questions avec les scripts.

Article traduit par Régis Baccaro