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

Article
06/13/2017

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

Notes

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

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 au journal des erreurs SQL Server et au 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 la définition de TRUSTWORTHY sur ON, consultez OPTIONS ALTER DATABASE SET (Transact-SQL).

À compter de SQL Server 2012, les PASSWORD options et ne MEDIAPASSWORD sont plus disponibles 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 ê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.

Utilisation de SQL Server Management Studio

Notes

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

Après vous être connecté à l’instance appropriée du Moteur de base de données SQL Server Microsoft, dans l’Explorateur d’objets, cliquez sur le nom du serveur pour développer son arborescence.
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.
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.
Dans la zone de Database 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.
Vous pouvez effectuer une sauvegarde de base de données pour tout mode de récupération (FULL, BULK_LOGGEDou SIMPLE).
Dans la zone de liste Type de sauvegarde, sélectionnez Complète.

Notez qu’après avoir créé une sauvegarde de base de données complète, 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).
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 du mécanisme des sauvegardes SQL Server conventionnelles. Pour plus d’informations, consultez Sauvegardes de copie uniquement (SQL Server).

Notes

Lorsque l'option Différentielle est sélectionnée, vous ne pouvez pas créer de sauvegarde de copie uniquement.
Pour Composant de sauvegarde, cliquez sur Database.
Acceptez le nom du jeu de sauvegarde par défaut proposé dans la zone de texte Nom , ou attribuez-lui un autre nom.
Dans la zone de texte Description , vous avez la possibilité de saisir une description du jeu de sauvegarde.
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.
Pour afficher ou sélectionner les options de support, cliquez sur Options de support dans le volet Sélectionner une page .
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.
  
  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 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 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 .
  
  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 lors de la sauvegarde dans le stockage Azure.
Dans la section Fiabilité, case activée éventuellement :
- Vérifier la sauvegarde en fin d'opération;
- Effectuer une somme de contrôle avant d'écrire sur le supportet éventuellement Continuer lors d'erreurs de somme de contrôle. Pour plus d’informations sur les sommes de contrôle, consultez Erreurs de média possibles pendant la sauvegarde et la restauration (SQL Server).
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 .

Notes

Les options de la section Journal des transactions sont inactives, à moins que vous ne sauvegardiez un journal des transactions (comme spécifié dans la section Type de sauvegarde de la page Général ).
Pour afficher ou sélectionner les options de sauvegarde, cliquez sur Options de sauvegarde dans le volet Sélectionner une page.
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 de sauvegarde, consultez BACKUP (Transact-SQL).
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
- Afficher ou configurer la compression par défaut des sauvegardes (option de configuration de serveur)
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).

Notes

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

Utilisation de Transact-SQL

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

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 pour une sauvegarde de base de données complète est la suivante :

BACKUP DATABASE database

TO unité_sauvegarde [ , ...n ]

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

Option	Description
database	Base 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 : {DISQUE \| 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.

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 Entreprise 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 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 = { '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 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**=** { 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)

R. Sauvegarde sur une unité de disque

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

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

Utilisez l'applet de commande Backup-SqlDatabase. Pour indiquer explicitement qu’il s’agit d’une sauvegarde de base de données complète, 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.
```
Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Database  
```

Pour configurer et utiliser le fournisseur SQL Server PowerShell

Fournisseur SQL Server PowerShell

Tâches associées

Voir aussi

Backup Overview (SQL Server)
Sauvegardes du journal des 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)

Partager via

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

Avant de commencer

Limitations et restrictions

Recommandations

Sécurité

Autorisations

Utilisation de SQL Server Management Studio

Pour sauvegarder une base de données

Utilisation de Transact-SQL

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

Exemples (Transact-SQL)

R. Sauvegarde sur une unité de disque

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

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

Utilisation de PowerShell

Tâches associées

Voir aussi

Ressources supplémentaires