By : Atif Shehzad | Mis à jour : 2009-06-15 | Commentaires (4) | Connexe : Plus >Sauvegarde

Problème

J’ai mis en place un plan de sauvegarde pour mes bases de données SQL Server. Je fais une sauvegarde complète quotidienne soutenue par des sauvegardes différentielles toutes les heures. Un plan de restauration est documenté sur la base de ce plan de sauvegarde avec l’emplacement et l’heure des sauvegardes mentionnées dans le plan. Il m’est souvent demandé de mettre à jour le serveur de test ou de développement avec une copie récente de la base de données de production. À cette fin, je fais une sauvegarde complète de la base de données requise. Le problème est que ces sauvegardes ad hoc interrompent ma séquence de récupération planifiée en cas de récupération nécessaire. Existe-t-il un moyen pour que mes sauvegardes ad hoc n’interrompent pas la séquence de mon plan de sauvegarde ?

Solution

Heureusement, à partir de SQL Server 2005, nous avons une option de sauvegarde pour ce genre de situation. Cette option de sauvegarde est connue sous le nom de sauvegardes par copie seulement. Cette option est spécifiquement destinée à créer une sauvegarde ad hoc qui ne perturbera pas la séquence de restauration planifiée pour cette base de données.

Les sauvegardes Copy Only peuvent être utilisées pour créer une sauvegarde complète ou une sauvegarde du journal des transactions. Cette option n’est pas mise en œuvre pour les sauvegardes différentielles. Dans des scénarios pratiques, vous aurez rarement besoin de créer une sauvegarde de journal de copie seule, cependant l’option de copie seule peut être fréquemment utilisée avec des sauvegardes complètes.

Bien que l’option de copie seule soit disponible pour SQL Server 2005, il n’y a pas loin pour les créer en utilisant SSMS dans SQL 2005. Si vous avez SSMS 2008, vous pouvez utiliser leGUI ou vous devez utiliser une instruction T-SQL pour créer des sauvegardes Copy Only.

D’abord regardons comment cela peut être fait en utilisant SSMS 2008 pour créer des sauvegardes Copy Only

• Allez dans la fenêtre de la base de données de sauvegarde comme vous le feriez pour une sauvegarde normale

• Juste en dessous du menu « Type de sauvegarde », vous trouverez une case à cocher pour « Copy OnlyBackup »
• Cliquez sur cette case
• Remplissez toutes les autres informations connexes comme vous le feriez pour une sauvegarde normale et cliquez sur OK

Note : La case à cocher pour « Copier seulement la sauvegarde » sera également active pour les sauvegardes différentielles, mais elle n’aura pas d’autre effet que de créer une sauvegarde différentielle normale.

Deuxièmement, pour créer une sauvegarde par copie seulement avec T-SQL, vous pouvez émettre la commande suivante:

-- Create full backup with Copy Only optionBACKUP DATABASE AdventureWorks TO DISK = 'D:\WithoutCopyOnly_AdventureWorks.bak'WITH COPY_ONLYGO

Preuve du concept

Avant d’aller plus loin, il sera bon de résumer le concept qu’une sauvegarde complète avec l’option de copie seulement est indépendante de la séquence de vos autres sauvegardes normales.Ainsi, après avoir créé une sauvegarde avec l’option copie seule, vous seriez en mesure de travailler avec le plan de récupération basé sur vos sauvegardes planifiées sans que cela ait un impact sur votre processus de restauration.

Nous utiliserons les informations LSN (log sequence number) pour suivre la sauvegarde complète qui est la base des sauvegardes différentielles. Tout d’abord, nous noterons le LSN de la base différentielle de la base de données AdventureWorks.

--Script # 1: Note current differential base LSNSELECT DB_NAME(database_id) AS ,differential_base_lsn AS 'Note differential base LSN'FROM sys.master_filesWHERE database_id = DB_ID('AdventureWorks')AND type_desc = 'ROWS'GO

Le LSN de la base différentielle affecte la séquence dans laquelle la combinaison de sauvegardes doit être restaurée dans une récupération. Vous pouvez également obtenir des informations détaillées sur le LSN de n’importe quelle base de données à partir de la table msdb..backupset.

Maintenant, nous devons confirmer qu’une sauvegarde complète sans l’option Copy Onlymettra à jour le differential_base_lsn. À cette fin, nous allons émettre une commande de sauvegarde complète sans l’option Copy Only et nous noterons le changement dans le LSNpour prouver que le LSN de la base différentielle mis à jour est le LSN de notre dernière sauvegarde complète.

--Script # 2: Create full backup and compare LSN information-- Create full backup -- Run script after changing Backup pathBACKUP DATABASE AdventureWorks TO DISK = 'D:\WithoutCopyOnly_AdventureWorks.bak'GO-- Get differential_base_lsn after full backupSELECT DB_NAME(database_id) AS ,differential_base_lsn AS 'Updated differential base LSN'FROM sys.master_filesWHERE database_id = DB_ID('AdventureWorks')AND type_desc = 'ROWS'GO-- Get LSN of recent full backup for match purposeSELECT database_name, backup_start_date, is_copy_only,first_lsn as 'LSN of full bakup'FROM msdb..backupset WHERE database_name = 'AdventureWorks'ORDER BY backup_start_date DESCGO

Dans l’ensemble de résultats suivant, nous pouvons vérifier que le LSN a été modifié pour la base de restauration de la sauvegarde différentielle et correspond à notre LSN de sauvegarde complète. Les deux LSN marqués ci-dessous sont les mêmes, ce qui confirme que la dernière sauvegarde est notre base différentielle.

Maintenant nous allons créer une sauvegarde complète avec l’option de copie seule et il sera prouvé que la sauvegarde complète avec l’option de copie seule n’affectera pas le LSN de la base différentielle de notre base de données. En d’autres termes, la sauvegarde complète avec l’option Copy Onlyn’affectera pas la sauvegarde complète de base pour les sauvegardes différentielles.

--Script # 3: Create full backup with copy only option and compare LSN information-- Create full backup with copy only option-- Run script after changing Backup pathBACKUP DATABASE AdventureWorks TO DISK = 'D:\CopyOnly_AdventureWorks.bak'WITH COPY_ONLYGO-- Get differential_base_lsn after full backup with copy only optionSELECT DB_NAME(database_id) AS ,differential_base_lsn AS 'Un changed differential base LSN'FROM sys.master_filesWHERE database_id = DB_ID('AdventureWorks')AND type_desc = 'ROWS'GO-- Get LSN of recent full backup with copy only option for match purposeSELECT database_name, backup_start_date, is_copy_only,first_lsn as 'LSN of last full bakup'FROM msdb..backupset WHERE database_name = 'AdventureWorks'ORDER BY backup_start_date DESCGO

Dans l’image suivante, nous pouvons vérifier qu’après une sauvegarde complète avec l’optionCopie seulement, le LSN de base différentiel est inchangé et il correspond au LSN de la sauvegarde complète précédente (les deux sont marqués en rouge). Notez également que la dernière sauvegarde complète avec l’option Copie seule est également là (marquée en vert).

Les considérations suivantes seront utiles lors de l’utilisation des sauvegardes avec l’option Copie seule.

• L’option Copie seule fonctionnera également pour les bases de données de niveau de compatibilité 80 dans une instance de SQL Server 2005
• Les sauvegardes de journaux de transaction avec l’option Copie seule préservent le point d’archivage de journal existant, donc il ne tronquera pas les journaux de transaction de cette base de données.
• Il n’y a pas de considération améliorée requise lors de la restauration d’une sauvegarde créée avec l’option Copie seule.
• Une sauvegarde complète avec l’option Copie seule ne peut pas être utilisée comme base pour restaurer des sauvegardes différentielles.
• Une sauvegarde de journal avec l’option Copie seule peut être créée pour les bases de données avec le modèle de récupération complet ou en vrac enregistré uniquement.
• Une sauvegarde complète avec l’option Copy Only peut être créée pour les bases de données avec n’importe quel modèle de récupération.

Next Steps

Alors que vous retirez toute sauvegarde de votre plan de sauvegarde, envisagez d’utiliser l’option Copy Only pour éviter toute confusion et perturbation de votre plan de récupération. Gardant à l’esprit que les sauvegardes en copie seule sont indépendantes des sauvegardes normales.

• Alors la prochaine fois que vous devez rafraîchir vos environnements de test ou de développementconsidérez l’utilisation de l’option de copie seule pour que vous n’interrompiez pas votre processus de récupération
• Pour des informations sur les modèles de récupération de base de données cliquezici ou ici
• Voici une astuce connexe sur les sauvegardes en copie seule

Dernière mise à jour : 2009-06-15

A propos de l’auteur

Atif Shehzad est un DBA SQL Server passionné, réviseur technique et auteur d’articles.
Voir tous mes conseils

Ressources connexes

• Plus de conseils pour les DBA SQL Server…