Création d’une sauvegarde d’instantané Transact-SQL
S’applique à :
SQL Server 2022 (16.x)
Cet article explique pourquoi et comment utiliser des sauvegardes d’instantanés Transact-SQL. Les sauvegardes d’instantanés Transact-SQL sont une nouvelle fonctionnalité de SQL Server 2022 (16.x).
Les bases de données sont de plus en plus volumineuses chaque jour. Traditionnellement, les sauvegardes SQL Server sont des sauvegardes en streaming. Une sauvegarde en streaming dépend de la taille de la base de données. Les opérations de sauvegarde consomment des ressources (processeur, mémoire, E/S et réseau) qui ont un impact sur le débit de la charge de travail OLTP simultanée pendant la durée de la sauvegarde. Une façon de rendre les performances de sauvegarde constantes, plutôt que de dépendre de la taille des données, consiste à effectuer une sauvegarde d’instantané à l’aide de mécanismes fournis par le matériel ou le service de stockage sous-jacent.
Étant donné que la sauvegarde proprement dite se produit au niveau matériel, il ne s’agit pas d’une solution SQL Server pure. SQL Server doit d’abord préparer les données et les fichiers journaux pour l’instantané afin de garantir que les fichiers se trouvent un état qui peut être restauré ultérieurement. Une fois cette opération effectuée, les opérations d’écriture sont suspendues sur SQL Server (les demandes de lecture sont toujours autorisées) et le contrôle est remis à l’application de sauvegarde pour finir de générer l’instantané. Cela fait, elle doit rendre le contrôle à SQL Server, où les opérations en écriture reprennent alors. Étant donné que les opérations en écriture doivent être gelées pendant la durée de l’opération d’instantané, il est essentiel que celle-ci se produise rapidement. L’objectif est que la charge de travail du serveur ne soit pas interrompue pendant une période prolongée. Par le passé, les utilisateurs se basaient sur des solutions tierces créées par-dessus le Service SQL Writer pour effectuer des sauvegardes d’instantanés. Le Service SQL Writer dépend de Windows VSS (Volume Shadow Service) avec SQL Server VDI (Virtual Device Interface) pour mener à bien l’orchestration entre SQL Server et l’instantané au niveau du disque. Les clients de sauvegarde basés sur le Service SQL Writer ont tendance à être complexes et ne fonctionnent que sur Windows. Avec les sauvegardes d’instantanés Transact-SQL, le côté SQL Server de l’orchestration peut être géré avec une série de commandes Transact-SQL. Cela permet aux utilisateurs de créer leurs propres petites applications de sauvegarde qui peuvent s’exécuter sur Windows ou Linux, voire des solutions de script si le stockage sous-jacent prend en charge une interface de script pour lancer un instantané.
Voici un exemple de script PowerShell qui illustre une solution de bout en bout de sauvegarde et de restauration d’une base de données dans une machine virtuelle IaaS Azure SQL à l’aide des fonctionnalités de sauvegarde d’instantané Transact-SQL introduites dans SQL Server 2022 (16.x) (et versions plus récentes).
Workflow
La syntaxe de la sauvegarde d’instantané Transact-SQL dissocie le mécanisme de capture instantanée dépendant du fournisseur des opérations de suspension et de sauvegarde. Elle vous permet d’effectuer les actions suivantes :
- Gelez une base de données avec la commande ALTER de façon à réaliser la capture instantanée du stockage sous-jacent, après quoi vous pouvez libérer la base de données et enregistrer l’instantané avec la commande BACKUP.
- Effectuez des captures instantanées de plusieurs bases de données simultanément avec les nouvelles commandes BACKUP GROUP et BACKUP SERVER. Avec cette option, les captures instantanées sont ainsi réalisées à la granularité de l’instantané du stockage sous-jacent, ce qui évite d’avoir à effectuer plusieurs fois une capture instantanée du même disque.
- Effectuez des sauvegardes complètes ainsi que des sauvegardes complètes COPY_ONLY. Ces sauvegardes sont également enregistrées dans
msdb. - Effectuez une récupération à un instant dans le passé à l’aide de sauvegardes de fichier journal effectuées suivant l’approche de streaming classique après la sauvegarde complète de l’instantané. Les sauvegardes différentielles en streaming sont également prises en charge si besoin.
Remarque
Les bitmaps différentielles sont effacées au cours de la première étape, lors de la suspension de la base de données avec la commande ALTER. Si l’utilisateur décide de libérer la base de données sans effectuer de sauvegarde, car la capture instantanée a échoué ou pour toute autre raison, la bitmap différentielle n’est pas valide. Par conséquent, toutes les sauvegardes différentielles suivantes sont plus gourmandes en E/S, car elles doivent analyser l’ensemble de la base de données. La bitmap différentielle redevient valide après une sauvegarde d’instantané réussie.
Le diagramme suivant illustre le flux de travail général des sauvegardes d’instantanés Transact-SQL :
L’étape de capture instantanée intermédiaire vous oblige à lancer la capture instantanée sur le stockage sous-jacent. Le diagramme suivant montre un exemple de fonctionnement d’un script de sauvegarde avec SQL Server pour mener à bien le processus de sauvegarde d’instantané :
De même, un script de restauration peut fonctionner comme suit :
Limites
Le nombre maximal de bases de données que vous pouvez sauvegarder avec cette fonctionnalité est de 64. Si plus de 64 bases de données sont présentes sur le serveur, l’erreur suivante s’affiche :
Error message:
Msg 925, Level 19, State 1, Line 4
Maximum number of databases used for each query has been exceeded. The maximum allowed is 64.
Exemples
Les sections suivantes montrent différentes commandes Transact-SQL utilisées pour effectuer une sauvegarde d’instantané sur disque. Lorsqu’une sauvegarde d’instantané est écrite sur disque, seules les métadonnées liées sont écrites dans le fichier. La sortie ne contient aucun contenu de la base de données, à l’exception de l’en-tête et du contenu du fichier. Le fichier d’interpréteur de commandes créé dans le cadre de l’exécution de la sauvegarde d’instantané doit être utilisé avec l’URI réel de l’instantané pour permettre une sauvegarde complète. Pour effectuer une restauration d’une base de données à partir de ce fichier, l’utilisateur doit copier les fichiers de base de données sur le point de montage à partir de l’URI d’instantané avant d’émettre la commande RESTORE. Les utilisateurs peuvent exécuter toutes les commandes Transact-SQL traditionnelles (RESTORE HEADERONLY, RESTORE FILELISTONLY, etc.) sur ce fichier de métadonnées de sauvegarde d’instantané, ainsi que RESTORE DATABASE. La syntaxe prend en charge l’écriture de métadonnées de sauvegarde d’instantané sur disque et sur URL. Les jeux de sauvegarde d’instantané peuvent également être ajoutés de la même manière que les jeux de sauvegarde en streaming dans un seul fichier.
Remarque
Pour la sauvegarde sur URL, les objets blob de blocs sont préférés, bien que les objets blob de pages soient acceptés pour SQL Server sur Windows. Pour SQL Server sur Linux et les conteneurs, seuls les objets blob de blocs sont pris en charge.
Suspension d’une base de données utilisateur unique pour la sauvegarde d’instantané et enregistrement d’une sauvegarde de base de données
ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
BACKUP DATABASE testdb1
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Suspension de plusieurs bases de données utilisateur pour la sauvegarde d’instantané
Si plusieurs bases de données se trouvent sur le même disque sous-jacent, elles peuvent être suspendues avec la commande suivante.
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));
BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Suspension de toutes les bases de données utilisateur sur le serveur pour la sauvegarde d’instantanés
Pour suspendre toutes les bases de données utilisateur sur le serveur, utilisez la commande suivante.
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
BACKUP SERVER
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Remarque
Aucune de ces commandes ne prend en charge la suspension des bases de données système : master, model et msdb pour la sauvegarde d’instantanés.
Suspension de plusieurs bases de données utilisateur avec une seule commande
Enregistrez l’instantané de toutes les bases de données utilisateur sur le serveur dans un seul jeu de sauvegarde :
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));
BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Remarque
Par défaut, la suspension des commandes de sauvegarde d’instantané efface la bitmap différentielle. Si vous préférez effectuer une sauvegarde avec copie uniquement, utilisez le mot clé COPY_ONLY comme dans les exemples suivants.
Exécution de sauvegardes d’instantanés avec copie uniquement
La bitmap différentielle est effacée avant que la base de données ne soit gelée. Cependant, la commande SUSPEND_FOR_SNAPSHOT_BACKUP fournit une option (COPY_ONLY) permettant de ne pas effacer la bitmap différentielle.
ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);
BACKUP DATABASE testdb1
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2), MODE = COPY_ONLY);
BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);
BACKUP SERVER
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Remarque
Il n’est pas nécessaire d’utiliser l’option COPY_ONLY sur la commande BACKUP, car il est déjà spécifié lors de la suspension de la base de données pour la sauvegarde d’instantané.
Balisage du jeu de sauvegardes
Les options MEDIANAME et MEDIADESCRIPTION peuvent être utilisées dans la commande de sauvegarde pour baliser l’URI associé au instantané. Cette utilisation permet au fichier de sauvegarde de transporter les informations d’instantané sous-jacentes, ainsi que les métadonnées de la base de données. Vous pouvez également utiliser les options NAME et DESCRIPTION pour baliser l’URI avec l’instantané de jeu de sauvegardes individuel.
SQL Server n’interprète les informations LABEL d’aucune manière. Cette option permet toutefois d’afficher l’URI associé à la sauvegarde d’instantané avec la commande RESTORE LABELONLY.
Vous pouvez ensuite attacher les disques d’instantané situés à l’URI à la machine virtuelle pour restaurer l’instantané. L’URI d’instantané stocké dans MEDIANAME et MEDIADESCRIPTION sera également disponible pour l’affichage par la suite dans la table msdb.dbo.backupmediaset de la base de données msdb.
Sortie de la sauvegarde d’instantané avec RESTORE HEADERONLY
La sortie avec RESTORE HEADERONLY se présente comme suit si la base de données, le groupe et le serveur sont exécutés en séquence et écrits dans le même fichier de sortie :
RESTORE HEADERONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;
Sortie de la sauvegarde d’instantané avec RESTORE FILELISTONLY
La sortie avec RESTORE FILELISTONLY affiche le premier jeu de sauvegarde par défaut :
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;
Filtrage de la sortie RESTORE FILELISTONLY dans un jeu de sauvegarde
Pour sélectionner spécifiquement un certain jeu de sauvegarde parmi plusieurs avec RESTORE FILELISTONLY, utilisez la clause FILE, qui est déjà prise en charge sur RESTORE FILELISTONLY.
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3;
Filtrage de la sortie RESTORE FILELISTONLY dans une base de données
Pour sélectionner spécifiquement une base de données unique parmi plusieurs dans le jeu de sauvegarde sélectionné avec RESTORE FILELISTONLY, utilisez la clause FILE avec la nouvelle clause DBNAME. La clause DBNAME ne peut être employée que sur des jeux de sauvegarde d’instantané.
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3';
Restauration d’une base de données d’instantanés
La restauration d’une base de données à partir d’une sauvegarde d’instantané ressemble à l’attachement d’une base de données. Exécutez la commande de restauration sans l’option RECOVERY si la base de données doit être attachée sans récupération. Par défaut, RESTORE sélectionne la première base de données du jeu de sauvegarde d’instantané. L’exemple suivant illustre la restauration de testdb1. Si testdb1 existe déjà sur le serveur, incluez la clause REPLACE. Vous devez monter les fichiers de base de données avant d’exécuter la commande RESTORE.
RESTORE DATABASE testdb1
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, REPLACE, --> no DBNAME clause - restore first database in backup set
MOVE 'testdb1' TO 'd:\temp\snap\testdb1.mdf',
MOVE 'testdb1_log' TO 'd:\temp\snap\testdb1_log.ldf';
Restauration d’une base de données d’instantanés qui figure au milieu de la liste
Si la base de données à restaurer se trouve au milieu, spécifiez-la avec la clause DBNAME. La syntaxe suivante permet de restaurer la base de données spécifiée dans la clause DBNAME.
RESTORE DATABASE testdb3
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> restores testdb3 database
MOVE 'testdb3' TO 'd:\temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'd:\temp\snap\testdb3_log.ldf',
NORECOVERY;
Restauration de la base de données sous un autre nom
Vous pouvez restaurer la base de données sous un autre nom. Si la base de données à restaurer se trouve au milieu, spécifiez-la avec la clause DBNAME. La syntaxe suivante permet de restaurer la base de données spécifiée avec la clause DBNAME et de la renommer testdb33.
RESTORE DATABASE testdb33 --> renames the specified database testdb3 to testdb33.
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> original name specified here
MOVE 'testdb3' TO 'd:\temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'd:\temp\snap\testdb3_log.ldf',
NORECOVERY;
Extraction de bases de données d’un jeu de sauvegarde contenant plusieurs bases de données avec RESTORE BACKUPSETONLY
Un jeu de sauvegarde d’instantané contenant plusieurs bases de données d’un instantané de groupe ou de serveur peut être fractionné avec la commande RESTORE BACKUPSETONLY. En résulte un jeu de sauvegarde par base de données.
Si un instantané de serveur comporte trois bases de données dans un fichier de sauvegarde contenant un seul jeu de sauvegarde, la commande suivante génère trois jeux de sauvegarde (un par base de données). Cela crée un répertoire avec <file_name_prefix>_<unique_time_stamp> les fichiers de sortie.
RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db1.bkm'
WITH METADATA_ONLY;
Extraction d’une base de données spécifique d’un jeu de sauvegarde contenant plusieurs bases de données avec RESTORE BACKUPSETONLY
RESTORE BACKUPSETONLY prend en charge le paramètre DBNAME, qui permet à l’utilisateur de générer une seule base de données sur les trois bases de données du jeu de sauvegarde. La commande accepte également le paramètre FILE pour filtrer plusieurs jeux de sauvegarde dans le fichier de sauvegarde.
RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb2';
Vues de gestion dynamique (DMV) pour voir l’état de suspension et les verrous acquis
sys.dm_server_suspend_status (db_id, db_name, suspend_session_id, suspend_time_ms, is_diffmap_cleared, is_writeio_frozen)
sys.dm_tran_locks (resource_type, resource_database_id, resource_lock_partition, request_mode, request_type, request_status, request_owner_type, request_session_id)
Répertorier les détails du jeu de sauvegardes pour les sauvegardes d’instantanés T-SQL
SELECT database_name,
type,
backup_size,
backup_start_date,
backup_finish_date,
is_snapshot
FROM msdb.dbo.backupset
WHERE is_snapshot = 1;
Propriétés au niveau du serveur et de la base de données permettant de vérifier si une base de données a été suspendue pour la sauvegarde d’instantané
SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup');
Exemple de script de résolution des problèmes T-SQL
L’exemple de script T-SQL suivant peut être utilisé pour détecter les bases de données suspendues sur le serveur et les réactiver si nécessaire.
IF (SERVERPROPERTY('IsServerSuspendedForSnapshotBackup') = 1)
BEGIN
--full server suspended, requires server level thaw
PRINT 'Full server is suspended, requires server level thaw'
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF
END
ELSE
BEGIN
IF (SERVERPROPERTY('SuspendedDatabaseCount') > 0)
BEGIN
DECLARE @curdb SYSNAME
DECLARE @sql NVARCHAR(500)
DECLARE mycursor CURSOR FAST_FORWARD
FOR
SELECT db_name
FROM sys.dm_server_suspend_status;
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @curdb
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'unfreezing DB ' + @curdb
SET @sql = 'ALTER DATABASE ' + @curdb + ' SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF'
EXEC sp_executesql @SQL
FETCH NEXT
FROM mycursor
INTO @curdb
END
PRINT 'All DB unfrozen'
CLOSE mycursor;
DEALLOCATE mycursor;
END
ELSE
-- no suspended database, thus no user action needed.
PRINT 'No database/server is suspended for snapshot backup'
END
Voir aussi
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour