Créer une sauvegarde complète de base de données (SQL Server)

Cette rubrique explique comment créer une sauvegarde de base de données complète dans SQL Server 2012 à l'aide de SQL Server Management Studio, de Transact-SQL ou de PowerShell.

[!REMARQUE]

À compter de la mise à jour cumulative 2 de SQL Server 2012 SP1, la sauvegarde SQL Server vers le service de stockage d'objets blob Windows Azure est prise en charge. Pour plus d'informations, consultez Backup and Restore Enhancements et Sauvegarde et restauration SQL Server avec le service de stockage d'objets blob Windows Azure.

Dans cette rubrique

• Avant de commencer :

  Limitations et restrictions

  Recommandations

  Sécurité

• Pour créer une sauvegarde de base de données complète, utilisez :

  SQL Server Management Studio

  Transact-SQL

  PowerShell

• Tâches associées

Avant de commencer

Limitations et restrictions

• L'instruction BACKUP n'est pas autorisée dans une transaction explicite ou implicite.

• Les sauvegardes créées avec une version plus récente de SQL Server ne peuvent pas être restaurées dans les versions antérieures de SQL Server.

• Pour plus d'informations, consultez Vue d'ensemble de la sauvegarde (SQL Server).

Recommandations

• À mesure que la taille d'une base de données augmente, les sauvegardes complètes de base de données nécessitent davantage de temps et d'espace de stockage. Par conséquent, pour les bases de données volumineuses, il est conseillé de compléter les sauvegardes complètes avec une série de sauvegardes différentielles de base de données. Pour plus d'informations, consultez Sauvegardes différentielles (SQL Server).

• Vous pouvez estimer la taille d'une sauvegarde complète de base de données en utilisant la procédure stockée système sp_spaceused.

• Par défaut, chaque opération de sauvegarde réussie ajoute une entrée dans le journal des erreurs SQL Server et dans le journal des événements système. Si vous sauvegardez très fréquemment le journal, ces messages de réussite peuvent rapidement s'accumuler, créer des journaux d'erreurs très volumineux et compliquer la recherche d'autres messages. Dans de tels cas, vous pouvez supprimer ces entrées de journal en utilisant l'indicateur de trace 3226 si aucun de vos scripts ne dépend de ces entrées. Pour plus d'informations, consultez Indicateurs de trace (Transact-SQL).

Sécurité

TRUSTWORTHY a la valeur OFF pour une sauvegarde de base de données. Pour plus d'informations sur l'affectation de la valeur ON à TRUSTWORTHY, consultez Options SET de ALTER DATABASE (Transact-SQL).

À compter de SQL Server 2012, les options PASSWORD et MEDIAPASSWORD sont supprimées pour la création de sauvegardes. Vous pouvez toujours restaurer les sauvegardes créées avec des mots de passe.

Autorisations

Les autorisations BACKUP DATABASE et BACKUP LOG sont définies par défaut aux membres du rôle serveur fixe sysadmin et des rôles de base de données fixes db_owner et db_backupoperator.

Des problèmes de propriété et d'autorisations sur le fichier physique de l'unité de sauvegarde sont susceptibles de perturber une opération de sauvegarde. SQL Server doit être en mesure de lire et d'écrire sur l'unité ; le compte sous lequel le service SQL Server s'exécute doit avoir des autorisations d'écriture. Toutefois, sp_addumpdevice, qui ajoute une entrée pour une unité de sauvegarde dans les tables système, ne vérifie pas les autorisations d'accès au fichier. De tels problèmes pour le fichier physique de l'unité de sauvegarde peuvent n'apparaître que lorsque la ressource physique est sollicitée au moment de la sauvegarde ou de la restauration.

[Haut de la page]

Utilisation de SQL Server Management Studio

[!REMARQUE]

Si vous spécifiez une tâche de sauvegarde à l'aide de SQL Server Management Studio, vous pouvez générer le script Transact-SQL BACKUP correspondant en cliquant sur le bouton Script et en sélectionnant une destination de script.

Pour sauvegarder une base de données

1. Après la connexion à l'instance appropriée du Microsoft Moteur de base de données SQL Server, dans l'Explorateur d'objets, cliquez sur le nom du serveur pour développer son arborescence.

2. Développez Bases de données puis, selon la base de données, sélectionnez une base de données utilisateur ou développez Bases de données système et sélectionnez une base de données système.

3. Cliquez avec le bouton droit sur la base de données, pointez sur Tâches, puis cliquez sur Sauvegarder. La boîte de dialogue Sauvegarder la base de données s'affiche.

4. Dans la zone de liste Base de données, vérifiez le nom de la base de données. Vous pouvez éventuellement sélectionner une autre base de données dans la liste.

5. Il est possible d'effectuer une sauvegarde de base de données pour tout mode de récupération (FULL, BULK_LOGGED ou SIMPLE).

6. Dans la zone de liste Type de sauvegarde, sélectionnez Complète.

   Notez qu'après la création d'une sauvegarde de base de données complète, vous pouvez créer une sauvegarde de base de données différentielle ; pour plus d'informations, consultez Créer une sauvegarde différentielle de base de données (SQL Server).

7. Vous pouvez si vous le souhaitez sélectionner Sauvegarde de copie uniquement pour créer une sauvegarde de copie uniquement. Une sauvegarde de copie uniquement est une sauvegarde SQL Server indépendante de la séquence des sauvegardes SQL Server conventionnelles. Pour plus d'informations, consultez Sauvegardes de type copie seule (SQL Server).

   [!REMARQUE]

   Lorsque l'option Différentielle est sélectionnée, vous ne pouvez pas créer de sauvegarde de copie uniquement.

8. Pour l'option Composant de sauvegarde, cliquez sur Base de données.

9. Acceptez le nom du jeu de sauvegarde par défaut proposé dans la zone de texte Nom, ou attribuez-lui un autre nom.

10. Dans la zone de texte Description, vous avez la possibilité de saisir une description du jeu de sauvegarde.

11. Spécifiez le moment où le jeu de sauvegarde va expirer et pourra être remplacé sans ignorer explicitement la vérification des données d'expiration :

    • Pour que le jeu de sauvegarde expire au bout d'un nombre de jours spécifique, cliquez sur Après (option par défaut) et entrez le nombre de jours souhaité pour l'expiration du jeu après sa création. Cette valeur peut varier entre 0 et 99999 jours ; la valeur 0 indique que le jeu de sauvegarde n'expirera jamais.

      La valeur par défaut est définie dans l'option Délai de rétention par défaut du support de sauvegarde (jours) de la boîte de dialogue Propriétés du serveur (page Paramètres de base de données). Pour y accéder, cliquez avec le bouton droit sur le nom du serveur dans l'Explorateur d'objets et sélectionnez les propriétés. Ensuite, sélectionnez la page Paramètres de base de données.

    • Pour que le jeu de sauvegarde expire à une date spécifique, cliquez sur Le et entrez la date d'expiration souhaitée.

      Pour plus d'informations sur les dates d'expiration des sauvegardes, consultez BACKUP (Transact-SQL).

12. Choisissez le type de destination de la sauvegarde : Disque ou Bande. Pour sélectionner les chemins de 64 lecteurs de bande ou disques (maximum) contenant un seul support de sauvegarde, cliquez sur Ajouter. Les chemins d'accès sélectionnés apparaissent dans la zone de liste Sauvegarde sur.

    Pour supprimer une destination de sauvegarde, sélectionnez-la puis cliquez sur Supprimer. Pour afficher le contenu d'une destination de sauvegarde, sélectionnez-la, puis cliquez sur Sommaire.

13. Pour afficher ou sélectionner les options avancées, cliquez sur Options dans le volet Sélectionner une page.

14. Sélectionnez une option Remplacer le support en cliquant sur un des éléments suivants :

    • Sauvegarder sur le support de sauvegarde existant

      Pour cette option, cliquez sur Ajouter au jeu de sauvegarde existant ou sur Remplacer tous les jeux de sauvegarde existants. Pour plus d'informations, consultez Jeux de supports, familles de supports et jeux de sauvegarde (SQL Server).

      Vous pouvez aussi activer la case à cocher Vérifier le nom du support de sauvegarde et la date d'expiration du jeu de sauvegarde pour forcer l'opération de sauvegarde à vérifier la date et l'heure de l'expiration du support de sauvegarde ou un jeu de sauvegarde.

      Vous pouvez éventuellement entrer un nom dans la zone de texte Nom du support de sauvegarde. Si aucun nom n'est spécifié, un support de sauvegarde avec un nom vide est créé. Si vous spécifiez un nom pour le support de sauvegarde, ce support (bande ou disque) est vérifié pour voir si le nom réel correspond bien au nom que vous entrez ici.

    • Sauvegarder sur un nouveau support de sauvegarde et effacer tous les jeux de sauvegarde existants

      Pour cette option, entrez un nom dans la zone de texte Nouveau nom du support de sauvegarde et décrivez éventuellement le jeu de supports dans la zone de texte Description du nouveau support de sauvegarde.

15. Dans la section Fiabilité, vous pouvez activer les cases à cocher :

    • Vérifier la sauvegarde en fin d'opération ;

    • Effectuer une somme de contrôle avant d'écrire sur le support et éventuellement Continuer lors d'erreurs de somme de contrôle. Pour obtenir des informations sur les sommes de contrôle, consultez Erreurs de support possibles pendant les opérations de sauvegarde et de restauration (SQL Server).

16. Si vous effectuez la sauvegarde sur un lecteur de bande (spécifié dans la section Destination de la page Général), l'option Décharger la bande après la sauvegarde est active. Vous pouvez cliquer sur cette option pour activer l'option Rembobiner la bande avant de décharger.

    [!REMARQUE]

    Les options de la section Journal des transactions sont inactives sauf si vous effectuez la sauvegarde sur un journal des transactions (comme indiqué dans la section Type de sauvegarde de la page Général).

17. SQL Server 2008 Enterprise et versions ultérieures prennent en charge la compression de la sauvegarde. Par défaut, la compression d'une sauvegarde dépend de la valeur de l'option de configuration de serveur backup-compression default. Toutefois, quelle que soit la valeur par défaut actuelle au niveau du serveur, vous pouvez compresser une sauvegarde en activant Compresser la sauvegarde, et vous pouvez empêcher la compression en activant Ne pas compresser la sauvegarde.

    Pour consulter ou modifier la valeur par défaut de compression de la sauvegarde actuelle

    • Afficher ou configurer l'option de configuration du serveur valeur par défaut de compression de la sauvegarde

[!REMARQUE]

Vous pouvez également utiliser l'Assistant Plan de maintenance pour créer des sauvegardes de bases de données.

[Haut de la page]

Utilisation de Transact-SQL

Pour créer une sauvegarde de base de données complète

1. Exécutez l'instruction BACKUP DATABASE en spécifiant les éléments suivants :

   • le nom de la base de données à sauvegarder ;

   • l'unité de sauvegarde où est écrite la sauvegarde complète de la base de données.

   La syntaxe Transact-SQL de base nécessaire pour une sauvegarde de base de données complète est la suivante :

   BACKUP DATABASE database

   TO backup_device [ ,...n ]

   [ WITH with_options [ ,...o ] ] ;

   Option	Description
   database	Base de données à sauvegarder
   backup_device [ ,...n ]	Spécifie une liste de 1 à 64 unités de sauvegarde à utiliser pour l'opération de sauvegarde. Vous pouvez spécifier une unité de sauvegarde physique ou une unité de sauvegarde logique correspondante, si celle-ci est déjà définie. Pour spécifier une unité de sauvegarde physique, utilisez l'option DISK ou TAPE : { DISK | TAPE } = physical_backup_device_name Pour plus d'informations, consultez Unités de sauvegarde (SQL Server).
   WITH with_options [ ,...o ]	Spécifie éventuellement une ou plusieurs options supplémentaires, o. Pour obtenir des informations de base sur les options, consultez l'étape 2.

2. Spécifiez éventuellement une ou plusieurs options WITH. Quelques options WITH de base sont décrites ici. Pour obtenir des informations sur toutes les options WITH, consultez BACKUP (Transact-SQL).

   • Options WITH de base relatives au jeu de sauvegarde :

     • { COMPRESSION | NO_COMPRESSION }
       Dans SQL Server 2008 Enterprise et les versions ultérieures uniquement, spécifie si la compression de sauvegarde est effectuée sur cette sauvegarde, remplaçant la valeur par défaut au niveau du serveur.

     • DESCRIPTION = { 'text' | **@**text_variable }
       Spécifie le texte au format libre servant à décrire le jeu de sauvegarde. La chaîne peut compter jusqu'à 255 caractères.

     • NAME = { backup_set_name | **@**backup_set_name_var }
       Spécifie le nom du jeu de sauvegarde. Les noms peuvent contenir jusqu'à 128 caractères. Si l'option NAME n'est pas spécifiée, le nom reste vide.

   • Options WITH de base relatives au jeu de sauvegarde :

     Par défaut, l'option BACKUP ajoute la sauvegarde à un support de sauvegarde existant, préservant les jeux de sauvegarde existants. Pour spécifier explicitement ceci, utilisez l'option NOINIT. Pour plus d'informations sur l'ajout à des jeux de sauvegardes existants, consultez Jeux de supports, familles de supports et jeux de sauvegarde (SQL Server).

     Une autre méthode pour formater le support de sauvegarde consiste à utiliser l'option FORMAT :

     • FORMAT [ , MEDIANAME**=** { media_name | **@**media_name_variable } ] [ , MEDIADESCRIPTION = { text | **@**text_variable } ]
       Utilisez la clause FORMAT si vous utilisez le support pour la première fois ou si vous souhaitez écraser toutes les données existantes. Assignez éventuellement un nom et une description au nouveau support.

       Important

       Soyez extrêmement vigilant lorsque vous utilisez la clause FORMAT de l'instruction BACKUP, car elle entraîne la destruction de toutes les sauvegardes préalablement stockées sur le support de sauvegarde.

Exemples (Transact-SQL)

A.Sauvegarde sur une unité de disque

L'exemple suivant sauvegarde entièrement la base de données AdventureWorks2012 sur disque, à l'aide de FORMAT, pour créer une nouveau jeu de supports.

USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'
   WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of AdventureWorks2012';
GO

B.Sauvegarde sur un périphérique à bandes

L'exemple suivant sauvegarde la base de données AdventureWorks2012 complète sur bande, en ajoutant la sauvegarde aux sauvegardes précédentes.

USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
   TO TAPE = '\\.\Tape0'
   WITH NOINIT,
      NAME = 'Full Backup of AdventureWorks2012';
GO

C.Sauvegarde sur un périphérique à bandes logique

L'exemple suivant crée une unité de sauvegarde logique pour un périphérique à bandes. Il sauvegarde ensuite la base de données AdventureWorks2012 complète sur ce périphérique.

-- Create a logical backup device, 
-- AdventureWorks2012_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'AdventureWorks2012_Bak_Tape', '\\.\tape0'; 
USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
   TO AdventureWorks2012_Bak_Tape
   WITH FORMAT,
      MEDIANAME = 'AdventureWorks2012_Bak_Tape',
      MEDIADESCRIPTION = '\\.\tape0', 
      NAME = 'Full Backup of AdventureWorks2012';
GO

[Haut de la page]

Utilisation de PowerShell

1. Utilisez l'applet de commande Backup-SqlDatabase. Pour indiquer explicitement qu'il s'agit d'une sauvegarde complète de la base de données, spécifiez le paramètre -BackupAction avec sa valeur par défaut, Database. Ce paramètre est facultatif pour les sauvegardes complètes de base de données.

   L'exemple suivant crée une sauvegarde complète de la base de données MyDB à l'emplacement de sauvegarde par défaut de l'instance de serveur Computer\Instance. Cet exemple spécifie, de manière facultative, -BackupAction Database.

   --Enter this command at the PowerShell command prompt, C:\PS>
   Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Database
   

Pour configurer et utiliser le fournisseur SQL Server PowerShell

• Fournisseur PowerShell SQL Server

[Haut de la page]

Tâches associées

• Créer une sauvegarde complète de base de données (SQL Server)

• Créer une sauvegarde différentielle de base de données (SQL Server)

• Restaurer une sauvegarde de base de données (SQL Server Management Studio)

• Restaurer une sauvegarde de base de données en mode de récupération simple (Transact-SQL)

• Restaurer une base de données jusqu'au point d'échec en mode de récupération complète (Transact-SQL)

• Restaurer une base de données à un nouvel emplacement (SQL Server)

• Utiliser l'Assistant Plan de maintenance

[Haut de la page]

Voir aussi

Référence

sp_addumpdevice (Transact-SQL)

BACKUP (Transact-SQL)

Sauvegarder la base de données (page Général)

Sauvegarder la base de données (page Options)

Concepts

Vue d'ensemble de la sauvegarde (SQL Server)

Sauvegardes du journal de transactions (SQL Server)

Jeux de supports, familles de supports et jeux de sauvegarde (SQL Server)

Sauvegardes différentielles (SQL Server)

Sauvegardes complètes de bases de données (SQL Server)