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 2016 à l’aide de SQL Server Management Studio, de Transact-SQL ou de PowerShell.

Pour plus d’informations sur la sauvegarde SQL Server dans le service de stockage d’objets blob Microsoft Azure, consultez Sauvegarde et restauration SQL Server avec le service de stockage d’objets blob Microsoft Azure et Sauvegarde SQL Server vers une URL.

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 obtenir une vue d’ensemble et approfondir vos connaissances des concepts de sauvegarde et des tâches, consultez Vue d’ensemble de la sauvegarde (SQL Server) avant de continuer.

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. 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) et Sauvegarde SQL Server vers une URL.

  • 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 au journal des erreurs SQL Server et au journal des événements système. Si vous effectuez régulièrement une sauvegarde, ces messages de réussite s’accumuleront rapidement et vos journaux d’erreurs deviendront énormes. Cela peut rendre la recherche d’autres messages difficile. Dans ces cas-là, vous pouvez supprimer ces entrées de journaux de sauvegarde 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 la façon d’affecter la valeur ON à TRUSTWORTHY, consultez Options ALTER DATABASE SET (Transact-SQL).

À compter de SQL Server 2012 , les options PASSWORD et MEDIAPASSWORD sont suspendues 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 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 pouvoir lire et é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.

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.

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é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 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. Vous pouvez 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 données en copie seule est une sauvegarde SQL Server indépendante du mécanisme des sauvegardes SQL Server conventionnelles. Pour plus d’informations, consultez Sauvegardes de copie uniquement (SQL Server).

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

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

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

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

  4. Choisissez le type de destination de la sauvegarde en cliquant sur Disque, Bande ou URL. Pour sélectionner les chemins d'accès à 64 lecteurs de bande ou de disque au 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.

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

  6. 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 jeu de supports ou du 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.

      System_CAPS_ICON_important.jpg Important


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

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

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

      System_CAPS_ICON_important.jpg Important


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

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

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

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

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

  10. 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 doit être comprise entre 0 et 99999 jours ; une valeur de 0 jour signifie 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 Leet entrez la date d'expiration souhaitée.

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

  11. 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 Compression par défaut des sauvegardes. 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

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

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

Exemples

A. Sauvegarde du service de stockage d’objets blob Microsoft Azure

Étapes courantes

Les trois exemples suivants effectuent une sauvegarde complète de la base de données Sales vers le service de stockage d’objets blob Microsoft Azure. Le nom du compte de stockage est mystorageaccount. Le conteneur se nomme myfirstcontainer. Par souci de concision, les quatre premières étapes ne sont répertoriées ici qu’une seule fois et tous les exemples commencent à l’Étape 5.

  1. Dans l’Explorateur d’objets, connectez-vous à une instance du moteur de base de données SQL Server et développez-la.

  2. Développez Bases de données, cliquez avec le bouton droit sur Sales, pointez sur Tâches, puis cliquez sur Sauvegarder.

  3. Dans la page Général de la section Destination, sélectionnez URL dans la liste déroulante Sauvegarde sur.

  4. Cliquez sur Ajouter pour ouvrir la boîte de dialogue Sélectionner la destination de la sauvegarde.

    1. Sauvegarde distribuée vers une URL quand il existe déjà des informations d’identification SQL Server
    Une stratégie d’accès stockée a été créée avec des droits de lecture, écriture et liste. Les informations d’identification SQL Server, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, ont été créées à l’aide d’une signature d’accès partagé associée à la stratégie d’accès stockée.

    1. Sélectionnez https://mystorageaccount.blob.core.windows.net/myfirstcontainer dans la zone de texte Conteneur de stockage Windows Azure.

    2. Dans la zone de texte Fichier de sauvegarde, entrez Sales_stripe1of2_20160601.bak.

    3. Cliquez sur OK.

    4. Répétez les étapes 4 et 5.

    5. Dans la zone de texte Fichier de sauvegarde, entrez Sales_stripe2of2_20160601.bak.

    6. Cliquez sur OK.

    7. Cliquez sur OK.

    2. Il existe une signature d’accès partagé mais pas d’informations d’identification SQL Server

    1. Entrez https://mystorageaccount.blob.core.windows.net/myfirstcontainer dans la zone de texte Conteneur de stockage Windows Azure.

    2. Entrez la signature d’accès partagé dans la zone de texte Stratégie d’accès partagé.

    3. Cliquez sur OK.

    4. Cliquez sur OK.

    3. Il n’existe aucune signature d’accès partagé

    1. Cliquez sur le bouton Nouveau conteneur pour ouvrir la boîte de dialogue Se connecter à un abonnement Microsoft.

    2. Terminez la boîte de dialogue Se connecter à un abonnement Microsoft et cliquez sur OK pour revenir à la boîte de dialogue Sélectionner la destination de la sauvegarde. Pour plus d’informations, consultez Se connecter à un abonnement Microsoft Azure.

    3. Cliquez sur OK dans la boîte de dialogue Sélectionner la destination de la sauvegarde.

    4. Cliquez sur OK.

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 unité_sauvegarde [ ,...n ]

    [ WITH options_with [ ,...o ] ] ;

    OptionDescription
    Base de donnéesBase de données à sauvegarder
    unité_sauvegarde [ ,...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 } =nom_unité_sauvegarde_physique

    Pour plus d’informations, consultez Unités de sauvegarde (SQL Server).
    WITH options_with [ ,...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 plus d’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 versions ultérieures uniquement, spécifie si la compression de la sauvegarde est effectuée sur cette sauvegarde, remplaçant la valeur par défaut au niveau du serveur.

      ENCRYPTION (ALGORITHM, SERVER CERTIFICATE |ASYMMETRIC KEY)
      Dans SQL Server 2014 ou les versions ultérieures, spécifiez l'algorithme de chiffrement à utiliser, ainsi que le certificat ou la clé asymétrique pour sécuriser le chiffrement.

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

      NAME = { nom_jeu_sauvegarde | @var_nom_jeu_sauvegarde }
      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 sauvegarde 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= { nom_support | @variable_nom_support } ] [ , MEDIADESCRIPTION = { texte | @variable_texte } ]
      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.

      System_CAPS_ICON_important.jpg 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  

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.

Exemples

A. Sauvegarde locale complète

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  

B. Sauvegarde complète sur Microsoft Azure

L’exemple suivant crée une sauvegarde complète de la base de données Sales sur l’instance MyServer pour le service de stockage d’objets blob Microsoft Azure. Une stratégie d’accès stockée a été créée avec des droits de lecture, écriture et liste. Les informations d’identification SQL Server, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, ont été créées à l’aide d’une signature d’accès partagé associée à la stratégie d’accès stockée. La commande PowerShell utilise le paramètre BackupFile pour spécifier l’emplacement (URL) et le nom du fichier de sauvegarde.

import-module sqlps;
$container = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer';
$FileName = 'Sales.bak';
$database = 'Sales';
$BackupFile = $container + '/' + $FileName ;
  
Backup-SqlDatabase -ServerInstance "MyServer" –Database $database -BackupFile $BackupFile;

Pour configurer et utiliser le fournisseur SQL Server PowerShell

Dépannage des opérations de sauvegarde et de restauration SQL Server
Vue d’ensemble de la sauvegarde (SQL Server)
Sauvegardes des journaux de transactions (SQL Server)
Jeux de supports, familles de supports et jeux 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 bases de données (SQL Server)

Ajouts de la communauté

AJOUTER
Afficher: