Créer une sauvegarde de base de données complète

Article
07/12/2024

S’applique à : SQL Server

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

Pour plus d’informations, consultez Sauvegarde et restauration SQL Server avec Stockage Blob Azure et Sauvegarde SQL Server vers une URL.

Limitations et restrictions

L’instruction BACKUP n’est pas autorisée dans une transaction explicite ni 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, songez à compléter les sauvegardes complètes avec une série de sauvegardes différentielles de base de données.
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 fréquemment des sauvegardes, les messages de réussite s’accumulent rapidement, ce qui crée des journaux des erreurs très volumineux et complique la recherche d’autres messages. 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 obtenir des informations sur la façon d’affecter la valeur ON à TRUSTWORTHY, consultez Options ALTER DATABASE SET (Transact-SQL).

À partir de SQL Server 2012 (11.x), les options PASSWORD et MEDIAPASSWORD ne 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. Le service SQL Server lit et écrit sur l’appareil. Le compte sous lequel le service SQL Server s’exécute doit disposer d’autorisations d’accès en écriture sur l’unité de sauvegarde. 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. Les problèmes dans le fichier physique de l’unité de sauvegarde risquent de ne pas s’afficher tant que la sauvegarde n’est pas utilisée ou qu’une restauration n’a pas été effectuée.

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.

Après vous être connecté à l'instance appropriée du moteur de base de données Microsoft SQL Server, dans l'Explorateur d'objets, développez l'arborescence du serveur.
Développez Bases de données, puis 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 à sauvegarder, pointez sur Tâches, puis sélectionnez Sauvegarder….
Dans la boîte de dialogue Sauvegarder la base de données, la base de données que vous avez sélectionnée apparaît dans la liste déroulante (vous pouvez la remplacer par toute autre base de données sur le serveur).
Dans la liste déroulante Type de sauvegarde, sélectionnez un type de sauvegarde. La valeur par défaut est Complète.

Important

Vous devez effectuer au moins une sauvegarde complète de la base de données avant d’effectuer une sauvegarde différentielle ou du journal des transactions.
Sous Composant de sauvegarde, sélectionnez Base de données.
Dans la section Destination, passez en revue l’emplacement par défaut du fichier de sauvegarde (dans le dossier ../mssql/data).

Vous pouvez utiliser la liste déroulante Sauvegarder sur pour sélectionner un autre appareil. Sélectionnez Ajouter pour ajouter des objets de sauvegarde et des destinations. Vous pouvez distribuer le jeu de sauvegarde sur plusieurs fichiers pour une vitesse de sauvegarde accrue.

Pour supprimer une destination de sauvegarde, sélectionnez-la, puis sélectionnez Supprimer. Pour afficher le contenu d’une destination de sauvegarde existante, sélectionnez-la, puis sélectionnez Contenu.
(facultatif) Passez en revue les autres paramètres disponibles dans les pages Options de support et Options de sauvegarde.

Pour plus d’informations sur les différentes options de sauvegarde, consultez Page Général, Page Options de support et Page Options de sauvegarde.
Sélectionnez OK pour démarrer la sauvegarde.
Une fois la sauvegarde terminée, sélectionnez OK pour fermer la boîte de dialogue SQL Server Management Studio.

Informations supplémentaires

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 ou une sauvegarde du journal des transactions.
(facultatif) Vous pouvez cocher la case 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 classique des sauvegardes SQL Server. Pour plus d’informations, consultez Sauvegardes de copie uniquement (SQL Server). La sauvegarde avec copie uniquement n’est pas disponible pour le type de sauvegarde Différentielle.
L’option Remplacer le support est désactivée sur la page Options de support si vous sauvegardez vos données vers une URL.

Exemples

Pour les exemples suivants, créez une base de données de test avec le code Transact-SQL suivant :

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 DATETIME NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

R : Sauvegarde complète sur disque à l’emplacement par défaut

Dans cet exemple, la base de données SQLTestDB est sauvegardée sur disque à l’emplacement de sauvegarde par défaut.

Après vous être connecté à l'instance appropriée du moteur de base de données Microsoft SQL Server, dans l'Explorateur d'objets, développez l'arborescence du serveur.
Développez Bases de données, cliquez avec le bouton droit sur SQLTestDB, pointez sur Tâches, puis sélectionnez Sauvegarder….
Cliquez sur OK.
Une fois la sauvegarde terminée, sélectionnez OK pour fermer la boîte de dialogue SQL Server Management Studio.

Effectuer la sauvegarde SQL

B. Sauvegarde complète sur disque à un emplacement autre que celui par défaut

Dans cet exemple, la base de données SQLTestDB est sauvegardée sur disque à l’emplacement de votre choix.

Après vous être connecté à l'instance appropriée du moteur de base de données Microsoft SQL Server, dans l'Explorateur d'objets, développez l'arborescence du serveur.
Développez Bases de données, cliquez avec le bouton droit sur SQLTestDB, pointez sur Tâches, puis sélectionnez Sauvegarder….
Dans la page Général de la section Destination , sélectionnez Disque dans la liste déroulante Sauvegarde sur : .
Sélectionnez Supprimer jusqu’à ce que tous les fichiers de sauvegarde existants aient été supprimés.
Sélectionnez Ajouter pour ouvrir la boîte de dialogue Sélectionner la destination de la sauvegarde .
Entrez un chemin et un nom de fichier valides dans la zone de texte Nom de fichier et utilisez .bak comme extension pour simplifier la classification de ce fichier.
Sélectionnez OK, puis de nouveau OK pour lancer la sauvegarde.
Une fois la sauvegarde terminée, sélectionnez OK pour fermer la boîte de dialogue SQL Server Management Studio.

Changer l’emplacement de la base de données

C. Créer une sauvegarde chiffrée

Dans cet exemple, la base de données SQLTestDB est sauvegardée avec chiffrement à l’emplacement de sauvegarde par défaut.

Après vous être connecté à l'instance appropriée du moteur de base de données Microsoft SQL Server, dans l'Explorateur d'objets, développez l'arborescence du serveur.
Développez Bases de données, Bases de données système, cliquez avec le bouton droit sur master, puis sélectionnez Nouvelle requête pour ouvrir une fenêtre Requête avec une connexion à votre base de données SQLTestDB.

Exécutez les commandes suivantes pour créer une clé principale de base de données et un certificat dans la base de données master.

-- Create the master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  

-- If the master key already exists, open it in the same session that you create the certificate (see next step)
OPEN MASTER KEY DECRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'

-- Create the certificate encrypted by the master key
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';

Dans l’Explorateur d’objets, cliquez avec le bouton droit sur SQLTestDB dans le nœud Bases de données, pointez sur Tâches, puis sélectionnez Sauvegarder….
Dans la page Options de support, dans la section Remplacer le support, sélectionnez Sauvegarder sur un nouveau support de sauvegarde et effacer tous les jeux de sauvegarde existants.
Dans la page Options de sauvegarde de la section Chiffrement sélectionnez la case à cocher Chiffrer la sauvegarde .
Dans la liste déroulante Algorithme, sélectionnez AES 256.
Dans la liste déroulante Certificat ou clé asymétrique , sélectionnez MyCertificate.
Cliquez sur OK.

Sauvegarde chiffrée

D. Sauvegarder vers le stockage Blob Azure

L’exemple ci-dessous crée une sauvegarde complète de SQLTestDB vers le stockage Blob Azure. Il part du principe que vous disposez déjà d’un compte de stockage doté d’un conteneur d’objets blob. Il crée automatiquement une signature d’accès partagé, et échoue si le conteneur en possède déjà une.

Si vous ne disposez pas d’un conteneur d’objets stockage Blob Azure dans un compte de stockage, créez-en un avant de continuer. Consultez Création d’un compte de stockage universel et Création d’un conteneur.

Après vous être connecté à l'instance appropriée du moteur de base de données Microsoft SQL Server, dans l'Explorateur d'objets, développez l'arborescence du serveur.
Développez Bases de données, cliquez avec le bouton droit sur SQLTestDB, pointez sur Tâches, puis sélectionnez Sauvegarder….
Dans la page Général de la section Destination , sélectionnez URL dans la liste déroulante Sauvegarde sur .
Sélectionnez Ajouter pour ouvrir la boîte de dialogue Sélectionner la destination de la sauvegarde .
Si vous avez déjà inscrit le conteneur de stockage Azure que vous souhaitez utiliser avec SQL Server Management Studio, sélectionnez-le. Sinon, sélectionnez Nouveau conteneur pour inscrire un nouveau conteneur.
Dans la boîte de dialogue Se connecter à un abonnement Microsoft, connectez-vous à votre compte.
Dans la zone de texte déroulante Sélectionner un compte de stockage, sélectionnez votre compte de stockage.
Dans la zone de texte déroulante Sélectionner un conteneur d’objets blob, sélectionnez votre conteneur d’objets blob.
Dans la zone de calendrier déroulante Expiration de la stratégie d’accès partagé, sélectionnez une date d’expiration pour la stratégie d’accès partagé que vous créez dans cet exemple.
Sélectionnez Créer des informations d’identification pour générer une signature d’accès partagé et des informations d’identification dans SQL Server Management Studio.
Sélectionnez OK pour fermer la boîte de dialogue Se connecter à un abonnement Microsoft.
Dans la zone de texte Fichier de sauvegarde, modifiez le nom du fichier de sauvegarde (facultatif).
Sélectionnez OK pour fermer la boîte de dialogue Sélectionner une destination de sauvegarde.
Sélectionnez OK pour démarrer la sauvegarde.
Une fois la sauvegarde terminée, sélectionnez OK pour fermer la boîte de dialogue SQL Server Management Studio.

Remarque

La sauvegarde sur le stockage Blob Azure n’est actuellement pas prise en charge à l’aide d’identités managées.

Utilisation de Transact-SQL

Créez une sauvegarde de base de données complète en exécutant l’instruction BACKUP DATABASE et 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 ] ] ;

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 ]	Permet de spécifier une ou plusieurs options 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 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 (10.0.x) Enterprise et les versions ultérieures uniquement, spécifie si la compression des sauvegardes est effectuée sur cette sauvegarde, 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 en forme libre qui décrit 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 le nom n’est pas spécifié, il est vide.

Par défaut, BACKUP ajoute la sauvegarde à un support de sauvegarde existant, préservant les jeux de sauvegarde existants. Pour le spécifier explicitement, 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).

Pour mettre en forme le support de sauvegarde, utilisez 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.

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

Pour les exemples suivants, créez une base de données de test avec le code Transact-SQL suivant :

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
   ID INT NOT NULL PRIMARY KEY,
   c1 VARCHAR(100) NOT NULL,
   dt1 DATETIME NOT NULL DEFAULT GETDATE()
)
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

R : Sauvegarder sur une unité de disque

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

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of SQLTestDB';
GO

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

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

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

C. Sauvegarder 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 SQLTestDB complète sur ce périphérique.

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

Utilisation de PowerShell

Utilisez l’applet de commande Backup-SqlDatabase . Pour indiquer explicitement 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.

Remarque

Ces exemples nécessitent le module SqlServer. Pour déterminer s’il est installé, exécutez Get-Module -Name SqlServer. Pour installer ce module, exécutez Install-Module -Name SqlServer dans une session administrateur de PowerShell.

Pour plus d’informations, consultez Fournisseur PowerShell SQL Server.

Important

Si vous ouvrez une fenêtre PowerShell dans SQL Server Management Studio pour vous connecter à une installation de SQL Server, vous pouvez ignorer la partie relative aux informations d’identification. En effet, les informations d’identification présentes dans SSMS sont automatiquement utilisées pour établir la connexion entre PowerShell et votre instance SQL Server.

Exemples

R. Sauvegarde complète (locale)

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

Pour des exemples complets de syntaxe, consultez Backup-SqlDatabase.

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. Sauvegarde complète vers Azure

L’exemple suivant crée une sauvegarde complète de la base de données <myDatabase> sur l’instance <myServer> pour le service Stockage Blob 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/<myContainer>) 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.

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential

Partager via

Créer une sauvegarde de base de données complète

Limitations et restrictions

Recommandations

Sécurité

autorisations

Utilisation de SQL Server Management Studio

Informations supplémentaires

Exemples

R : Sauvegarde complète sur disque à l’emplacement par défaut

B. Sauvegarde complète sur disque à un emplacement autre que celui par défaut

C. Créer une sauvegarde chiffrée

D. Sauvegarder vers le stockage Blob Azure

Utilisation de Transact-SQL

Exemples

R : Sauvegarder sur une unité de disque

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

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

Utilisation de PowerShell

Exemples

R. Sauvegarde complète (locale)

B. Sauvegarde complète vers Azure

Tâches associées

Commentaires

Ressources supplémentaires

Partager via

Créer une sauvegarde de base de données complète

Limitations et restrictions

Recommandations

Sécurité

autorisations

Utilisation de SQL Server Management Studio

Informations supplémentaires

Exemples

R : Sauvegarde complète sur disque à l’emplacement par défaut

B. Sauvegarde complète sur disque à un emplacement autre que celui par défaut

C. Créer une sauvegarde chiffrée

D. Sauvegarder vers le stockage Blob Azure

Utilisation de Transact-SQL

Exemples

R : Sauvegarder sur une unité de disque

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

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

Utilisation de PowerShell

Exemples

R. Sauvegarde complète (locale)

B. Sauvegarde complète vers Azure

Tâches associées

Contenu connexe

Commentaires

Ressources supplémentaires