En avez-vous assez de restaurer manuellement chaque base de données sur un nouveau serveur lorsque le serveur d’origine tombe en panne ? Le processus manuel vous semble-t-il lent et sujet aux erreurs de frappe et de clic de souris ? Souhaitez-vous que ces scripts de restauration soient automatiquement générés, de sorte que vous n’ayez qu’à les lancer ? Eh bien, cet article vous montrera une méthode possible pour accélérer et réduire les erreurs lors de la restauration de toutes les bases de données sur un serveur.
La plupart des administrateurs de bases de données (DBA) élaborent des procédures pour récupérer un serveur en cas de défaillance. Certaines organisations ont la chance de disposer du matériel/logiciel nécessaire pour prendre en charge un serveur de secours et effectuer une expédition de journaux afin de minimiser les temps d’arrêt. Cependant, toutes les organisations ne peuvent pas soutenir financièrement le modèle de serveur de secours. Notre entreprise fait partie de ces organisations à court de fonds. Comme toute autre organisation, notre objectif est de récupérer rapidement tout serveur défaillant, compte tenu des outils et du matériel dont nous disposons. Par conséquent, nous avons développé deux procédures stockées pour aider à accélérer notre temps de récupération.
La première procédure stockée, “usp_build_restore_script”, construit un script de restauration contenant les commandes RESTORE DATABASE et RESTORE LOG pour chaque base de données sur un serveur. La deuxième procédure stockée, “usp_what_files_to_restore”, produit une liste de fichiers de sauvegarde qui doivent être restaurés à partir de la bande, et qui sont nécessaires au script de restauration. Chacune de ces procédures stockées prend en compte les fichiers de sauvegarde associés à l’ensemble de sauvegarde actuel, c’est-à-dire l’ensemble de fichiers de sauvegarde nécessaires pour restaurer chaque base de données à partir de la dernière sauvegarde complète. Le script de restauration produit permettra de récupérer toutes les bases de données jusqu’au point de la dernière sauvegarde.
Pourquoi avons-nous construit ces procédures stockées ? Comme la plupart des entreprises, nous avons décidé de pratiquer la récupération d’un de nos serveurs de production, nous avons donc planifié un test de reprise après sinistre. Lors de notre premier exercice de reprise après sinistre, nous avons identifié deux points à améliorer. L’un était de réduire le temps nécessaire pour restaurer les sauvegardes de bases de données à partir de la bande, et l’autre était de réduire le temps et les efforts manuels associés à la restauration de toutes les bases de données.
Notre premier exercice de reprise après sinistre a restauré l’ensemble du répertoire de sauvegarde qui contenait deux semaines de fichiers de sauvegarde de bases de données. Normalement, notre récupération de base de données ne nécessiterait que l’ensemble le plus récent de sauvegardes (une complète, une différentielle et/ou quelques sauvegardes de journaux de transactions). Chaque fichier supplémentaire qui était restauré inutilement à partir de la bande augmentait le temps total nécessaire pour copier les sauvegardes de bases de données à partir de la bande et augmentait donc le temps d’arrêt. Notre objectif était de minimiser les temps d’arrêt.
Pour optimiser le temps de restauration à partir de la bande, nous avons développé la procédure stockée “usp_what_files_to_restore” pour identifier un ensemble spécifique de fichiers de sauvegarde de bases de données associés au dernier cycle de sauvegarde (complète, différentielle et journaux de transactions) qui devaient être récupérés. Deuxièmement, nous avons remarqué que la restauration des bases de données une par une manuellement à l’aide de l’Enterprise Manager était un processus lent et sujet aux erreurs. Ce processus de restauration manuelle pouvait facilement être automatisé pour réduire la durée et les erreurs associées à la restauration manuelle des bases de données. Cela nous a conduit à développer la procédure stockée “usp_build_restore_script” pour produire un script de restauration de base de données qui restaurerait toutes les bases de données sur un serveur. Le script de restauration de base de données généré rationaliserait le processus de restauration en remplaçant les restaurations manuelles de bases de données effectuées via l’Enterprise Manager.
Le code de chacune de ces procédures stockées peut être trouvé ci-dessous. La procédure stockée “usp_build_restore_script” construit le script de restauration, tandis que la procédure stockée “usp_what_files_to_restore” produit une liste de fichiers à restaurer à partir de la bande.
Code pour usp_build_restore_script
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE PROCEDURE usp_build_restore_script AS -- Le code de la procédure stockée se trouve ici END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
Code pour usp_what_files_to_restore
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE PROCEDURE usp_what_files_to_restore AS -- Le code de la procédure stockée se trouve ici END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
Chaque procédure stockée comporte deux sections. La section 1 collecte les informations sur les fichiers de sauvegarde de base de données, et la section 2 utilise les informations de sauvegarde pour produire le script de restauration de base de données souhaité ou la liste des fichiers à restaurer à partir de la bande. La section 1 est essentiellement la même pour les deux procédures stockées. Cette section utilise les informations stockées dans les tables système backupset, backupmediaset et backupmediafamily de la base de données “msdb” pour recueillir les noms de fichiers physiques et les types de sauvegarde (complète, différentielle et journal) pour chaque sauvegarde de base de données. La section 2 est responsable de la production du script de restauration réel ou du rapport des fichiers de sauvegarde physiques à restaurer à partir de la bande.
Pour exécuter ces procédures stockées, vous pouvez utiliser la commande “osql” pour rediriger la sortie vers des fichiers physiques. Par exemple :
osql -E -Smyserver -Q"dba.dbo.usp_build_restore_script" -o c:\mssql\restore\restore_all.sql -w200 osql -E -Smyserver -Q"dba.dbo.usp_what_files_to_restore" -o c:\mssql\restore\files_to_restore.txt -w200
Vous pouvez ajouter ces commandes à votre tâche d’agent SQL Server qui crée vos sauvegardes de base de données. De plus, vous pouvez ajouter une étape à la même tâche de sauvegarde pour copier le script de restauration et la liste des fichiers de restauration vers un autre serveur sur un réseau. Cela garantit que vous disposez d’une copie du script et de la liste des fichiers à restaurer sur un autre serveur en cas de panne de votre boîte SQL Server.
Voici un exemple des commandes TSQL produites par “usp_build_restore_script” sur un serveur qui ne contenait qu’une seule base de données utilisateur :
-- Restaurer toutes les bases de données restore database DBA from disk = 'C:\mssql\MSSQL\BACKUP\DBA_20021005_2000_full.bak' with replace, norecovery go restore database DBA from disk = 'C:\mssql\MSSQL\BACKUP\DBA_20021006_2000_diff.bak' with replace, norecovery go restore log DBA from disk = 'C:\mssql\MSSQL\BACKUP\DBA_20021007_1000_tran.bak' with replace, norecovery go restore log DBA from disk = 'C:\mssql\MSSQL\BACKUP\DBA_20021007_1200_tran.bak' with replace go /* restore database master from disk = 'C:\mssql\MSSQL\BACKUP\master_20021006_2000_full.bak' with replace */go restore database model from disk = 'C:\mssql\MSSQL\BACKUP\model_20021006_2000_full.bak' with replace go restore database msdb from disk = 'C:\mssql\MSSQL\BACKUP\msdb_20021006_2000_full.bak' with replace go restore database Northwind from disk = 'C:\mssql\MSSQL\BACKUP\Northwind_20021006_2000_full.bak' with replace go restore database pubs from disk = 'C:\mssql\MSSQL\BACKUP\pub_20021006_2000_full.bak' with replace go
Voici une copie des fichiers à restaurer à partir de la bande créée par “usp_what_files_to_restore” à partir du même serveur :
C:\mssql\MSSQL\BACKUP\DBA_20021005_2000_full.bak C:\mssql\MSSQL\BACKUP\DBA_20021006_2000_diff.bak C:\mssql\MSSQL\BACKUP\DBA_20021007_1000_tran.bak C:\mssql\MSSQL\BACKUP\DBA_20021007_1200_tran.bak C:\mssql\MSSQL\BACKUP\master_20021006_2000_full.bak C:\mssql\MSSQL\BACKUP\model_20021006_2000_full.bak C:\mssql\MSSQL\BACKUP\msdb_20021006_2000_full.bak C:\mssql\MSSQL\BACKUP\Northwind_20021006_2000_full.bak C:\mssql\MSSQL\BACKUP\pubs_20021006_2000_full.bak
En conclusion, l’automatisation du processus de restauration de bases de données SQL Server peut considérablement accélérer le temps de récupération et réduire les erreurs. En mettant en œuvre des procédures comme celles décrites dans cet article, vous pouvez gagner du temps et réduire l’anxiété lors d’une situation stressante de reprise après sinistre.