Dans le chapitre d’ouverture du livre de Craig Mullin, Database Administration, il dit « À bien des égards, les affaires aujourd’hui sont des données ». Dans la plupart des organisations, la personne responsable de la protection des données est l’administrateur de la base de données you vous.

C’est vrai; toute l’entreprise est entre vos mains compétentes, fonctionnant sur ce serveur qui ne plante jamais, avec tous ces utilisateurs finaux qui ne font jamais d’erreurs en utilisant des applications, construites par ces développeurs qui écrivent du code sans faille la première fois, à chaque fois, avec l’aide de cette nouvelle coopérative qui a des privilèges « sa » grâce à votre patron.

OK. Arrête de pleurer. Il y a des choses que vous pouvez faire pour protéger les données SQL Server sous votre responsabilité et l’une des plus importantes est d’exécuter des sauvegardes de base de données régulières.

Sauvegardes

Microsoft, dans SQL Server Books Online, définit les sauvegardes comme:

Une copie de données utilisée pour restaurer et récupérer des données après une défaillance du système

Les sauvegardes SQL peuvent être créées de plusieurs façons et peuvent incorporer tout ou partie des données, ainsi qu’une partie du journal des transactions. Bien que cet article se concentre sur la syntaxe de 2005, la plupart des concepts sont applicables à 2000. C’est un sujet énorme. Au mieux, je vais gratter la surface et vous donner suffisamment d’informations pour que vous ne recommenciez pas à pleurer. Après avoir lu ceci, vous devriez pouvoir configurer un ensemble raisonnable de sauvegardes pour votre système.

Modèles de récupération

Afin de commencer à travailler sur les sauvegardes, les besoins métier définissent un modèle de récupération de base de données. En substance, un modèle de récupération définit ce que vous allez faire avec les données du journal des transactions.

Il existe trois modèles de récupération : Full, Simple et Bulk Logged. Ceux-ci sont assez faciles à définir:

• Simple – en mode de récupération simple, le journal des transactions n’est pas sauvegardé, vous ne pouvez donc récupérer que la sauvegarde complète ou différentielle la plus récente.
• Complet – en mode de récupération complète, vous sauvegardez la base de données et le journal des transactions afin de pouvoir récupérer la base de données à tout moment.
• Connecté en bloc En mode connecté en bloc, la plupart des transactions sont stockées dans le journal des transactions, mais certaines opérations en bloc telles que les chargements en bloc ou la création d’index ne sont pas enregistrées.

Les deux modes les plus couramment utilisés sont Simples et Complets. Ne supposez pas nécessairement que, bien sûr, vous devez toujours utiliser une récupération complète pour protéger vos données. C’est une décision d’affaires. L’entreprise va vous dire si vous avez besoin de récupérer à un moment donné ou si vous avez simplement besoin de la dernière sauvegarde complète. Il va définir si vos données sont récupérables par d’autres moyens, tels que la saisie manuelle, ou si vous devez protéger autant que possible car elles traversent le fil. Vous utilisez une récupération simple si vous pouvez vous permettre de perdre les données stockées depuis la dernière sauvegarde complète ou différentielle et / ou si vous n’avez tout simplement pas besoin de récupération à un moment donné. En mode simple, vous devez restaurer tous les groupes de fichiers de lecture / écriture secondaires lorsque vous restaurez le principal. Vous utilisez Simple principalement sur des bases de données secondaires qui ne sont pas une partie vitale absolue de l’entreprise ou des systèmes de reporting, avec un accès en lecture seule, donc il n’y a pas de journal des transactions à craindre de toute façon. Vous utilisez Full si chaque bit des données est vital, vous devez récupérer à un moment donné ou, généralement dans le cas de très grandes bases de données (VLDB), vous devez restaurer des fichiers individuels et des groupes de fichiers indépendamment des autres fichiers et groupes de fichiers.

Avec des modèles de récupération simples et complets, vous pouvez maintenant exécuter une sauvegarde en copie seule qui vous permet de copier la base de données dans un fichier de sauvegarde, mais n’affecte pas le journal, les programmes de sauvegarde différentiels ou la récupération d’impact à un moment donné. Je vais essayer d’explorer autant de ces sujets que possible à travers l’article, mais pas les fichiers et les groupes de fichiers.

Travailler avec une récupération simple

Assez de conversation. Passons à l’exécution des sauvegardes. Supposons que nous sommes en récupération simple sur une base de données de petite à moyenne taille. Je vais utiliser AdventureWorks pour tous les exemples de scripts. Pour le définir sur simple recovery:

Votre stratégie de sauvegarde la plus simple consiste à exécuter, à intervalles réguliers, la commande de sauvegarde SQL Server suivante, qui effectuera une sauvegarde complète de la base de données:

Qu’y a-t-il avec toutes les dactylographies que vous demandez? N’avons-nous pas des outils GUI pour gérer le travail pour nous? Oui, la plupart des sauvegardes simples peuvent être effectuées à l’aide de SQL Server Management Studio. Cependant, si vous voulez apprendre et comprendre ce que Management Studio fait pour vous, ou si vous voulez un contrôle précis sur ce qui est sauvegardé, comment et où, alors vous devrez sortir le clavier et ranger la souris.

La commande ci-dessus précipitera une sauvegarde de base sur le disque. La plupart des DBA que je connais sauvegardent dans un fichier, puis grattent les fichiers sur une bande ou un autre support. En effet, les fichiers sur disque sont simples et rapides à récupérer, alors que les médias peuvent parfois être un peu pénibles. Par exemple, nous avons généralement deux à trois jours de sauvegardes sur nos systèmes de fichiers pour une récupération immédiate. Nous n’allons aux systèmes de bandes que si nous avons besoin d’exécuter des restaurations pour des sauvegardes plus anciennes.

Qu’a fait cette commande ? Il a fait une copie de toutes les données validées dans la base de données. Il a également copié des entrées de journal non validées. Ceux-ci sont utilisés pendant la récupération pour valider ou annuler les modifications apportées aux données pendant le processus de sauvegarde.

Sauvegardes en copie uniquement

Normalement, la sauvegarde d’une base de données affecte d’autres processus de sauvegarde et de restauration. Par exemple, après avoir exécuté la commande précédente, toutes les sauvegardes différentielles (une sauvegarde qui ne copie que les données modifiées depuis la dernière sauvegarde) l’utiliseraient comme point de départ pour les modifications de données, pas la sauvegarde que vous avez exécutée la nuit dernière. Comme indiqué précédemment, SQL 2005 introduit un nouveau concept pour les sauvegardes, les sauvegardes COPY_ONLY, qui nous permettent de ne pas interrompre le cycle:

Nous avons déjà trouvé l’un de ces moments plus granulaires où le Studio de gestion ne vous aiderait pas. Si vous voulez une copie de sauvegarde uniquement, vous devez utiliser la ligne de commande.

Sauvegardes différentielles

Supposons un instant que nous sommes encore en simple récupération, mais nous avons affaire à une base de données plus grande, disons quelque chose de plus de 100 Go. Les sauvegardes complètes peuvent en fait commencer à ralentir un peu le processus. Au lieu de cela, après consultation avec l’entreprise, nous avons décidé de faire une sauvegarde complète hebdomadaire et des sauvegardes différentielles quotidiennes. Les sauvegardes différentielles sauvegardent uniquement les pages de données qui ont changé depuis la dernière sauvegarde complète. Voici la commande SQL backup pour effectuer une sauvegarde différentielle:

Maintenant, si nous devions restaurer cette base de données, nous irons d’abord à la dernière sauvegarde complète, la restaurerons, puis restaurerons les sauvegardes différentielles dans l’ordre (plus à ce sujet plus tard).

Il y a un certain nombre d’autres options de sauvegarde que je ne détaillerai pas ici. Lisez les livres en ligne pour voir les détails sur BLOCKSIZE, EXPIREDATE, RETAINDAYS, MOT DE PASSE, NOM, STATISTIQUES, etc.

Vous pouvez également exécuter une instruction qui vérifiera l’intégrité d’une sauvegarde de base de données. Il ne vérifie pas l’intégrité des données dans une sauvegarde, mais il vérifie que la sauvegarde est correctement formatée et accessible.

Récupération complète et sauvegardes de journaux

Nous avons principalement travaillé sur une base de données qui était en mode de récupération simple (cela s’appelait auparavant Truncate Log on Checkpoint). Dans ce mode, nous ne sauvegardons pas les journaux de transactions pour une récupération ultérieure. Chaque sauvegarde sous ce mécanisme est une sauvegarde de base de données. Les sauvegardes de journaux ne sont tout simplement pas possibles.

Cependant, vous n’avez protégé les données qu’à partir de la dernière bonne sauvegarde, complète ou différentielle. Changeons nos hypothèses. Nous avons maintenant affaire à une grande application et à une base de données critiques. Nous voulons pouvoir récupérer cette base de données jusqu’à la dernière minute. C’est un point très important. En théorie, puisque les entrées du journal sont stockées et sauvegardées, nous sommes protégés jusqu’à tout échec. Cependant, certaines défaillances peuvent entraîner une corruption du journal, rendant la récupération à un moment donné impossible. Nous devons donc déterminer quel sera le temps minimum raisonnable entre les sauvegardes de journaux. Dans ce cas, nous ne pouvons pas vivre avec plus de 15 minutes de données perdues.

Alors, commençons par mettre notre base de données en mode de récupération COMPLÈTE:

Ensuite, sur une base planifiée, dans ce cas toutes les 15 minutes, nous exécuterons la commande SQL backup pour le journal des transactions:

Ce script sauvegardera les transactions validées à partir du journal des transactions. Il a des marqueurs dans le fichier qui indiquent l’heure de début et d’arrêt. Il tronquera le journal lorsqu’il sera terminé avec succès, en supprimant du journal des transactions les transactions validées qui ont été écrites dans le fichier de sauvegarde. Si nécessaire, vous pouvez utiliser l’instruction WITH NO_TRUNCATE pour capturer des données du journal des transactions quel que soit l’état de la base de données, en supposant qu’elle soit en ligne et non en état d’URGENCE. C’est pour les urgences seulement.

Notez que nous n’utilisons pas l’instruction INIT dans ce cas, mais vous pouvez le faire si vous le souhaitez. Lorsque vous effectuez des sauvegardes de journaux, vous avez des options:

1. Exécutez toutes les sauvegardes dans un seul fichier, où elles s’empileront et tout ce que vous avez à faire, lors de la restauration (couverte plus tard), est de les parcourir.
2. Nommez les sauvegardes de manière unique, en utilisant probablement la date et l’heure dans la chaîne.

Dans ce dernier cas, la sécurité dit, utilisez INIT parce que vous exercez un contrôle maximal sur ce qui est sauvegardé où, et vous pourrez savoir exactement ce qu’est une sauvegarde, quand elle a été prise et d’où en fonction du nom. C’est encore un autre endroit où l’exploitation des sauvegardes à partir de la ligne de commande vous donne plus de contrôle que l’interface graphique. Nous avons utilisé les deux approches dans nos systèmes pour différentes raisons. Vous pouvez décider ce qui convient le mieux à vos besoins technologiques et commerciaux.

La plupart des options disponibles pour la sauvegarde de la base de données sont incluses dans la sauvegarde des journaux, y compris COPY_ONLY. Cela vous permettrait de capturer un ensemble de données de transaction sans affecter le journal ou la prochaine sauvegarde de journal planifiée. Cela serait pratique pour transférer les données de production vers un autre système pour le dépannage, etc.

Si votre base de données est définie sur Récupération COMPLÈTE, vous devez exécuter des sauvegardes de journaux. Parfois, les gens oublient et le journal des transactions s’agrandit au point de remplir le lecteur de disque. Dans ce cas, vous pouvez exécuter:

Attacher NO_LOG à la sauvegarde du journal, et ne pas spécifier d’emplacement pour le journal, entraîne la suppression de la partie inactive du journal, ce qui se fait sans entrée de journal elle-même, détruisant ainsi le lecteur de disque complet. Ceci n’est absolument pas recommandé car cela casse la chaîne de journaux, la série de sauvegardes de journaux à partir desquelles vous récupéreriez votre base de données à un moment donné. Microsoft recommande d’exécuter une sauvegarde complète immédiatement après l’utilisation de cette instruction. De plus, ils avertissent que cette instruction pourrait être obsolète dans une prochaine version.

Restauration de bases de données

Aussi importantes que soient les sauvegardes SQL Server, et elles sont vitales, elles sont inutiles sans la possibilité de restaurer la base de données.

Restauration d’une sauvegarde complète de base de données

La restauration d’une sauvegarde complète de base de données est aussi simple que de créer:

C’est vraiment aussi simple que cela – à moins que nous ne sauvegardions tout dans un fichier comme s’il s’agissait d’un périphérique de sauvegarde. Dans ce cas, vous devrez spécifier le fichier dans le « périphérique » auquel vous accédez. Si vous ne savez pas quel fichier, vous devrez générer une liste:

Cela vous donnera la même liste que celle que j’ai montrée ci-dessus de Management Studio. Alors maintenant, si nous voulions restaurer le deuxième fichier du groupe, la sauvegarde COPY_ONLY, vous émettrez la commande suivante:

Malheureusement, si vous suivez, vous constaterez peut-être que vous venez de générer cette erreur:

Cela signifie que votre base de données est en mode de récupération complète, mais que vous n’avez pas sauvegardé la « queue du journal », c’est-à-dire les transactions entrées depuis la dernière fois que vous avez effectué une sauvegarde. Vous pouvez remplacer cette exigence si vous modifiez la syntaxe précédente en:

C’est la première fois que nous empilons les clauses WITH (AVEC FILE= 2 et AVEC REPLACE est représenté comme AVEC FILE= 2, REPLACE), mais ce ne sera pas le dernier. Lisez les livres en ligne. La plupart des instructions de clause WITH peuvent être utilisées en combinaison avec les autres.

Que se passe-t-il si nous voulons restaurer une base de données différente de celle d’origine? Par exemple, nous voulons faire une copie de notre base de données à partir d’une sauvegarde séparée. Peut-être que nous voulons le déplacer vers un serveur de support de production où nous allons travailler dessus, séparé de la copie de production de la base de données. Si nous adoptons l’approche simple, eh bien, essayez ceci:

Dans ce cas, vous devriez voir toute une série d’erreurs liées au fait que les fichiers ne sont pas écrasés. Vous pouvez vraiment créer de nouvelles bases de données à partir de sauvegardes, mais si vous le faites sur un serveur avec la base de données existante, vous devrez modifier l’emplacement des fichiers physiques en utilisant les noms logiques. Afin de connaître les noms logiques des fichiers pour une base de données donnée, exécutez-le avant de tenter de déplacer les fichiers:

Cela peut ensuite être utilisé pour identifier les noms logiques appropriés afin de générer ce script:

Restauration d’une sauvegarde différentielle

La dernière méthode consiste à appliquer la sauvegarde différentielle. Cela nécessite deux étapes. Tout d’abord, nous allons restaurer la base de données, mais avec une torsion, puis nous appliquerons la sauvegarde différentielle:

La plupart de cela est probablement explicite sur la base de ce que nous avons déjà couvert. La seule ride est l’inclusion du mot clé NORECOVERY. Très simplement, lors d’une restauration, les transactions peuvent avoir commencé pendant le processus de sauvegarde. Certaines d’entre elles sont terminées et d’autres non. À la fin d’une restauration, les transactions terminées sont transférées dans la base de données et les transactions incomplètes sont annulées. La définition de NORECOVERY maintient les transactions ouvertes. Cela permet de récupérer l’ensemble suivant de transactions à partir de la sauvegarde suivante dans l’ordre.

Nous traitons principalement de sauvegardes et de restaurations simples dans cet article, mais une restauration plus avancée en 2005 permet de restaurer des groupes de fichiers secondaires lorsque la base de données est en ligne. Son groupe de fichiers principal doit être en ligne pendant l’opération. Cela sera plus utile pour les très grands systèmes de base de données.

La restauration des bases de données SQL Server à un moment donné

La restauration des journaux n’est pas beaucoup plus difficile que la restauration différentielle de la base de données que nous venons de terminer. Il y a juste un peu plus d’implication dans la restauration d’un moment dans le temps. En supposant que vous sauvegardez vos journaux sur un seul fichier ou appareil:

Sinon, vous allez simplement obtenir les noms de fichiers dont vous avez besoin. Exécutez d’abord la restauration de la base de données, en prenant soin de la laisser dans un état non récupéré. Suivez cela avec une série de restaurations de journaux à un moment donné.

Maintenant, ce que nous avons, c’est une base de données qui correspond exactement à la dernière transaction validée à 14h30: 29 le 23 octobre. N’oubliez pas que lors de restaurations en plusieurs étapes comme celle-ci, vous devez laisser la base de données dans un état de récupération. Cela signifie ajouter NORECOVERY à chaque instruction jusqu’à ce que vous ayez terminé le processus de restauration. Si, pour une raison quelconque, vous avez ajouté NORECOVERY à toutes vos déclarations, ou si vous vous arrêtez simplement au milieu et souhaitez remettre la base de données en ligne, vous pouvez utiliser cette déclaration pour terminer le processus:

Instantanés de base de données

SQL Server 2005 a introduit le concept d’instantané ou de vue statique en lecture seule d’une base de données. Les instantanés sont principalement créés afin de fournir une version en lecture seule d’une base de données à des fins de création de rapports. Cependant, ils fonctionnent de la même manière que les sauvegardes. La principale différence est que toutes les transactions non validées sont annulées. Il n’y a pas d’option pour avancer, capturer des journaux, etc., que les sauvegardes fournissent, et de très nombreuses ressources SQL Server ne sont pas utilisées du tout. La technologie de disque est plutôt utilisée pour créer une copie des données. Pour cette raison, ils sont beaucoup plus rapides que les sauvegardes à la fois pour créer et restaurer.

REMARQUE :
Pour plus de détails sur l’instantané SQL 2005, veuillez vous référer à http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/.

Une bonne utilisation des instantanés, en plus des rapports, peut être d’en créer un avant la maintenance après avoir déjà supprimé tous les utilisateurs actifs (et leurs transactions) du système. Bien que les instantanés ne prennent pas en charge la volatilité des sauvegardes en direct, leur vitesse et leur facilité de récupération en font un excellent outil pour une récupération rapide après un déploiement bâclé. Les instantanés sont stockés sur le serveur, vous devez donc vous assurer que vous disposez d’un stockage adéquat.

La syntaxe est différente car vous ne sauvegardez pas une base de données ; vous en créez une nouvelle:

Maintenant, il sera accessible pour un accès en lecture seule. Puisque nous sommes principalement concernés par l’utilisation de cela comme mécanisme de sauvegarde, incluons la méthode de restauration d’une base de données en un instantané de base de données.

Tout d’abord, identifiez l’instantané que vous souhaitez utiliser. S’il y en a plus d’un sur une base de données que vous allez restaurer, vous devrez tout supprimer sauf celui que vous utilisez:

Ensuite, vous pouvez restaurer la base de données en exécutant une instruction RESTORE (métaphores mixtes, pas bonnes):

C’est tout. Sur mon système, l’exécution des instantanés de base de données d’Adventureworks a pris 136 ms. La sauvegarde complète a pris 5 670 ms. La restauration de l’instantané a pris 905 ms et la restauration de la base de données a pris 13 382 ms. L’intégration de cela dans un processus de déploiement de production pourrait entraîner des avantages significatifs

Encore une fois, il convient de noter qu’il existe quelques mises en garde à l’utilisation de l’instantané. Vous devez disposer de suffisamment d’espace disque pour une deuxième copie de la base de données. Vous devez faire attention aux instantanés car la plupart de la syntaxe est similaire à celle utilisée par les bases de données elles-mêmes. Enfin, bien que des instantanés soient attachés à une base de données, vous ne pouvez pas exécuter de restauration à partir d’une sauvegarde de base de données de cette base de données.

Meilleures pratiques

La manière dont vous effectuez des sauvegardes de base de données ne doit pas être une décision technique. Cela devrait être dicté par l’entreprise. Les petits systèmes à faible taux de transaction et/ou les systèmes de reporting chargés régulièrement n’auront besoin que d’une sauvegarde complète de la base de données. Les systèmes de taille moyenne et les grands systèmes dépendent du type de données gérées pour déterminer les types de sauvegarde nécessaires.

Pour un système de taille moyenne, une sauvegarde quotidienne avec des sauvegardes de journaux pendant la journée répondrait probablement à la plupart des besoins en données en temps opportun.

Pour une grande base de données, la meilleure approche est de mélanger et de faire correspondre les sauvegardes pour assurer une récupérabilité maximale en un minimum de temps. Par exemple, exécutez une sauvegarde complète hebdomadaire. Deux fois par jour pendant la semaine, exécutez une sauvegarde différentielle. Toutes les 10 minutes pendant la journée, exécutez une sauvegarde de journal. Cela vous donne un grand nombre de mécanismes de récupération.

Pour les bases de données très volumineuses, vous devrez exécuter des sauvegardes de groupes de fichiers et de fichiers car une sauvegarde complète ou même une sauvegarde différentielle de la base de données complète peut ne pas être possible. Un certain nombre de fonctions supplémentaires sont disponibles pour vous aider dans ce domaine, mais je ne les aborderai pas ici.

Vous devriez prendre le temps de développer des scripts pour exécuter vos sauvegardes et restaurations. Une convention de nommage pour que vous sachiez quelle base de données, à partir de quel serveur, à partir de quelle date, dans quelle sauvegarde et quel format spécifiques seront très propices à votre santé mentale. Un emplacement commun pour les sauvegardes, journal, complet ou incrémental, doit être défini. Toute personne responsable doit être formée à la fois à la sauvegarde et à la récupération et au dépannage de la même manière. Il y a plusieurs façons de le faire, mais vous pouvez trouver quelques suggestions dans les sauvegardes Pop et les restaurations Pop.

Le vrai test consiste à exécuter vos mécanismes de sauvegarde, puis à exécuter une restauration. Ensuite, essayez un autre type de restauration, et un autre, et un autre. Assurez-vous que non seulement vous avez fait preuve de diligence raisonnable pour définir la façon de sauvegarder le système, mais que vous avez fait l’étape supplémentaire consistant à vous assurer que vous pouvez récupérer ces sauvegardes. Si vous n’avez pas pratiqué cela et documenté la pratique, puis testé le document, en effet, vous n’êtes pas prêt pour une catastrophe.

Résumé

Les sauvegardes au sein de votre entreprise devraient être comme voter à Chicago, tôt et souvent. La configuration des sauvegardes de base est assez simple. L’ajout de sauvegardes de journaux et de différentiels est également facile. Explorez les options pour voir comment ajouter des sauvegardes et des restaurations de fichiers et de groupes de fichiers pour augmenter la vitesse de vos sauvegardes et restaurations, ce qui augmentera la disponibilité du système et le temps de disponibilité. Gardez une norme de nommage commune. Soyez prudent lorsque vous utilisez des instantanés, mais utilisez-les certainement. Stockez vos fichiers dans un emplacement standard entre les serveurs. Pratiquez vos récupérations. Enfin, pour vraiment faire chanter vos sauvegardes, téléchargez un essai gratuit de la sauvegarde SQL de Red Gate. Il offre une compression haute performance et une résilience du réseau pour rendre le processus d’écriture ou de copie de sauvegardes sur des réseaux floconneux tolérant aux pannes.