Partager via

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

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

Remarque

Pour plus d’informations sur la sauvegarde SQL Server sur le service stockage Blob Azure, consultez sauvegarde et restauration SQL Server avec le service Stockage Blob Azure.

Dans cette rubrique

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 qu’une base de données augmente en taille complète, les sauvegardes de base de données prennent plus de temps et nécessitent davantage d’espace de stockage. Par conséquent, pour une base de données volumineuse, vous pouvez compléter une sauvegarde complète de base de données 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 à l’aide de la procédure stockée système sp_spaceused .

  • Par défaut, chaque opération de sauvegarde réussie ajoute une entrée au journal des erreurs SQL Server et au journal des événements système. Si vous sauvegardez le journal très fréquemment, ces messages de réussite s’accumulent rapidement, ce qui entraîne des journaux d’erreurs énormes qui peuvent rendre les autres messages difficiles. Dans ce cas, vous pouvez supprimer ces entrées de journal à l’aide de 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 est défini sur OFF sur une sauvegarde de base de données. Pour plus d’informations sur la définition de TRUSTWORTHY sur ON, consultez options ALTER DATABASE SET (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 des sauvegardes créées avec des mots de passe.

Autorisations

Les autorisations BACKUP DATABASE et BACKUP LOG reviennent 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. Ces problèmes sur le fichier physique de l’appareil de sauvegarde peuvent ne pas apparaître tant que la ressource physique n’est pas accessible lorsque la sauvegarde ou la restauration est tentée.

Utilisation de SQL Server Management Studio

Remarque

Lorsque 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 vous être connecté à l’instance appropriée du moteur de base de données Microsoft SQL Server, dans l’Explorateur d’objets, cliquez sur le nom du serveur pour développer l’arborescence du serveur.

  2. Développez Bases de donnéespuis, 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 Database zone de liste, 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. Vous pouvez effectuer une sauvegarde de base de données pour n’importe quel modèle 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 avoir créé une sauvegarde complète de base de données, vous pouvez créer une sauvegarde différentielle de base de données ; 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 de sauvegardes SQL Server conventionnelles. Pour plus d’informations, consultez Copy-Only Sauvegardes (SQL Server).

    Remarque

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

  8. Pour le composant de sauvegarde, cliquez sur Database.

  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. Si vous le souhaitez, dans la zone de texte Description, entrez une description du jeu de sauvegarde.

  11. Choisissez le type de destination de sauvegarde en cliquant sur Disque, Bande ou URL. Pour sélectionner les chemins d’accès d’un maximum de 64 lecteurs de disque ou de bande contenant un seul support, 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-le, puis cliquez sur Contenu.

  12. Pour afficher ou sélectionner les options multimédias, cliquez sur Options de média dans le volet Sélectionner une page .

  13. Sélectionnez une option Remplacer le média en cliquant sur l’une des options suivantes :

    • Sauvegarder sur l'ensemble de supports existant

      Pour cette option, cliquez sur Ajouter au jeu de sauvegarde existant ou remplacer tous les jeux de sauvegarde existants. Pour plus d’informations, consultez Media Sets, Media Families et Backup Sets (SQL Server).

      Si vous le souhaitez, sélectionnez Vérifier le nom du support multimédia et l’expiration du jeu de sauvegarde pour que l’opération de sauvegarde vérifie la date et l’heure à laquelle le jeu de supports et le jeu de sauvegarde expirent.

      Si vous le souhaitez, entrez un nom dans la zone de texte Nom du jeu de supports . Si aucun nom n’est spécifié, un jeu de médias avec un nom vide est créé. Si vous spécifiez un nom de jeu de supports média, le média (bande ou disque) est vérifié pour s'assurer que le nom réel correspond au nom que vous entrez ici.

      Important

      Cette option est désactivée si vous avez sélectionné l’URL comme destination de sauvegarde dans la page Général . Pour plus d’informations, consultez la page Sauvegarder une base de données (page Options multimédias)

      Si vous envisagez d’utiliser le chiffrement, ne sélectionnez pas cette option. Si vous sélectionnez cette option, les options de chiffrement de la page Options de sauvegarde sont désactivées. Le chiffrement n’est pas pris en charge lors de l’ajout au jeu de sauvegarde existant.

    • Créer une sauvegarde sur un nouveau jeu de supports et effacer toutes les séries de sauvegardes existantes

      Pour cette option, entrez un nom dans la zone de texte Nouveau nom du jeu de médias et, si nécessaire, décrivez le jeu de médias dans la zone de texte Nouvelle description du jeu de médias.

      Important

      Cette option est désactivée si vous avez sélectionné l’URL dans la page Général . Ces actions ne sont pas prises en charge lors de la sauvegarde dans le stockage Azure.

  14. Dans la section Fiabilité , vérifiez éventuellement :

  15. Si vous sauvegardez sur un lecteur de bande (comme spécifié dans la section Destination de la page Général), l'option Décharger la bande après la sauvegarde est active. Cliquez sur cette option pour activer l’option Rembobiner la bande avant déchargement.

    Remarque

    Les options de la section Journal des transactions sont inactives, sauf si vous sauvegardez un journal des transactions (comme spécifié dans la section Type de sauvegarde de la page Général ).

  16. Pour afficher ou sélectionner les options de sauvegarde, cliquez sur Options de sauvegarde dans le volet Sélectionner une page .

  17. Spécifiez quand le jeu de sauvegarde expirera et pourra être remplacé, sans avoir à passer explicitement par la vérification des données d'expiration :

    • Pour que le jeu de sauvegarde expire après un nombre spécifique de jours, cliquez sur Après (l’option par défaut) et entrez le nombre de jours après la création du jeu pour lesquels il expirera. Cette valeur peut être comprise entre 0 et 99999 jours ; une valeur de 0 jours signifie que le jeu de sauvegarde n’expirera jamais.

      La valeur par défaut est définie dans l’option de rétention du support de sauvegarde par défaut (en 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 ; puis sélectionnez la page Paramètres de la base de données .

    • Pour que le jeu de sauvegarde expire à une date spécifique, cliquez sur le bouton "On", puis entrez la date à laquelle le jeu expirera.

      Pour plus d’informations sur les dates d’expiration de sauvegarde, consultez BACKUP (Transact-SQL).

  18. SQL Server 2008 Enterprise et versions ultérieures prennent en charge la compression de sauvegarde. Par défaut, si une sauvegarde est compressée dépend de la valeur de l’option de configuration de serveur par défaut de compression de sauvegarde . Toutefois, quelle que soit la valeur par défaut du serveur actuel, vous pouvez compresser une sauvegarde en vérifiant compresser la sauvegarde et en empêchant la compression en vérifiant Ne pas compresser la sauvegarde.

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

  19. Spécifiez s’il faut utiliser le chiffrement pour la sauvegarde. Sélectionnez un algorithme de chiffrement à utiliser pour l’étape de chiffrement et fournissez un certificat ou une clé asymétrique dans une liste de certificats existants ou de clés asymétriques. Le chiffrement est pris en charge dans SQL Server 2014 ou version ultérieure. Pour plus d’informations sur les options de chiffrement, consultez la page Sauvegarder la base de données (page Options de sauvegarde).

Remarque

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

Utilisation de Transact-SQL

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

  1. Exécutez l’instruction BACKUP DATABASE pour créer la sauvegarde complète de la base de données, en spécifiant :

    • Nom de la base de données à sauvegarder.

    • Périphérique de sauvegarde où la sauvegarde complète de la base de données est écrite.

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

    Base de données BACKUP DATABASE

    TO backup_device [ ,... n ]

    [ WITH with_options [ ,... o ] ;

    Choix Descriptif
    base de données Base de données qui doit être sauvegardée.
    backup_device [ ,... n ] Spécifie une liste de 1 à 64 périphériques de sauvegarde à utiliser pour l’opération de sauvegarde. Vous pouvez spécifier un périphérique de sauvegarde physique ou spécifier un périphérique de sauvegarde logique correspondant, s’il est déjà défini. Pour spécifier un périphérique de sauvegarde physique, utilisez l’option DISK ou TAPE :

    { DISQUE | BANDE } =physical_backup_device_name

    Pour plus d’informations, consultez Périphériques de sauvegarde (SQL Server).
    WITH with_options [ ,... o ] Si vous le souhaitez, spécifie une ou plusieurs options supplémentaires, o. Pour plus d’informations sur certaines options de base, consultez l’étape 2.

  2. Si vous le souhaitez, spécifiez une ou plusieurs options WITH. Quelques options WITH de base sont décrites ici. Pour plus d’informations sur toutes les options WITH, consultez BACKUP (Transact-SQL).

    • Jeu de sauvegarde de base avec options :

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

      CHIFFREMENT (ALGORITHME, CERTIFICAT DE SERVEUR |CLÉ ASYMÉTRIQUE)
      Dans SQL Server 2014 ou version ultérieure uniquement, spécifiez l’algorithme de chiffrement à utiliser et la clé certificat ou asymétrique à utiliser pour sécuriser le chiffrement.

      DESCRIPTION = { 'text' | @text_variable }
      Spécifie le texte de forme libre qui décrit l'ensemble 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 du jeu de sauvegarde de base avec choix :

      Par défaut, BACKUP ajoute la sauvegarde à un ensemble de supports existant, en préservant les ensembles de sauvegarde existants. Pour spécifier cela explicitement, utilisez l’option NOINIT. Pour plus d’informations sur l’ajout à des jeux de sauvegarde existants, consultez Media Sets, Media Families et Backup Sets (SQL Server).

      Vous pouvez également mettre en forme le support de sauvegarde, utilisez l’option FORMAT :

      FORMAT [ , MEDIANAME**=** { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]
      Utilisez la clause FORMAT lorsque vous utilisez un média pour la première fois ou que vous souhaitez remplacer toutes les données existantes. Si vous le souhaitez, affectez au nouveau média un nom de média et une description.

      Important

      Utilisez une prudence extrême lorsque vous utilisez la clause FORMAT de l’instruction BACKUP, car cela détruit les sauvegardes précédemment stockées sur le support de sauvegarde.

Exemples (Transact-SQL)

Un. Sauvegarde sur un appareil de disque

L’exemple suivant sauvegarde la base de données complète AdventureWorks2012 sur disque, en utilisant FORMAT pour créer un nouvel ensemble 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 appareil à bande

L’exemple suivant sauvegarde la base de données AdventureWorks2012complè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

Chapitre C. Sauvegarde sur un périphérique de bande logique

L’exemple suivant crée un périphérique de sauvegarde logique pour un lecteur de bande. L’exemple sauvegarde ensuite la base de données AdventureWorks2012 complète sur cet appareil.

-- 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

Utilisation de PowerShell

  1. Utilisez l’applet de commande Backup-SqlDatabase. Pour indiquer explicitement qu’il s’agit d’une sauvegarde complète de 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 MyDB base de données vers l’emplacement de sauvegarde par défaut de l’instance Computer\Instancede serveur. Si vous le souhaitez, cet exemple spécifie -BackupAction Database.

    Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Database

Pour configurer et utiliser le fournisseur SQL Server PowerShell

Tâches associées

Voir aussi

Vue d’ensemble de la sauvegarde (SQL Server)
Sauvegardes du journal des transactions (SQL Server)
Ensembles de médias, familles de supports et ensembles de sauvegarde (SQL Server)
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 de sauvegarde)
Sauvegardes différentielles (SQL Server)
Sauvegardes complètes de base de données (SQL Server)

  • Last updated on