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 (T-SQL) ont été introduites dans 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 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, cette fonction ne constitue pas 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 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. Cette fonctionnalité 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’exemple utilise les fonctionnalités de sauvegarde d’instantané T-SQL introduites dans SQL Server 2022 (16.x).

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 :

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

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

3. Effectuez des sauvegardes FULL et COPY_ONLY FULL. Ces sauvegardes sont également enregistrées dans msdb.

4. 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 FULL de l’instantané. Les sauvegardes différentielles en streaming sont également prises en charge si besoin.

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 RESTORE 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 telles que RESTORE HEADERONLY et RESTORE FILELISTONLY 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 DISK 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.

R : 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;

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

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

D. Suspension de plusieurs bases de données utilisateur avec une seule commande

Enregistrez un 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;

E. Exécution de sauvegardes d’instantanés avec copie uniquement

Étant donné que l’image bitmap différentielle est effacée avant le gel, SUSPEND_FOR_SNAPSHOT_BACKUP fournit une option (COPY_ONLY) pour ne pas effacer la bitmap différentielle avant le gel.

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;

F. Sauvegarder une base de données avec des fichiers de données et des fichiers journaux sur différents lecteurs

Si vous disposez d’une base de données avec des fichiers de données (.mdf et .ndf) sur plusieurs lecteurs et que le fichier journal de transactions (.ldf) se trouve sur un autre lecteur, vous pouvez effectuer une sauvegarde d’instantané comme suit :

1. Suspendez la base de données (cela fige les E/S d’écriture sur les fichiers de données et les fichiers journaux).

   ALTER SERVER CONFIGURATION
   SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
   

2. Réalisez un instantané de tous les disques sous-jacents où les données de base de données et les fichiers journaux sont présents. Cette étape dépend du matériel.

3. Effectuez la sauvegarde à l’aide de l’option METADATA_ONLY, qui crée la sortie contenant les métadonnées de sauvegarde d’instantané (.bkm).

   BACKUP DATABASE testdb1
   TO DISK = 'D:\Temp\db.bkm'
   WITH METADATA_ONLY;
   

Pour restaurer cette sauvegarde à une étape ultérieure, procédez comme suit :

1. Montez ou attachez les disques d’instantanés sur la machine virtuelle où vous souhaitez restaurer.

2. Utilisez le fichier .bkm (dans l’étape 3 de la liste précédente) lorsque vous effectuez une restauration de base de données.

3. Si les lecteurs sont différents lors de la restauration, utilisez l’option MOVE permettant aux fichiers logiques de les placer dans la destination requise. Pour obtenir un exemple, consultez Exemple N.

G. 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 est également disponible à l’affichage dans la table de la base de données msdb dbo.backupmediaset.

H. Sortie de la sauvegarde d’instantané avec RESTORE HEADERONLY

La sortie avec RESTORE HEADERONLY se présente comme l’exemple suivant 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;

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

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

K. 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 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';

L. 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';

M. Restauration d’une base de données d’instantanés qui figure au milieu de la liste

Si la base de données à RESTORED 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;

N. 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 à RESTORED 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;

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

P. 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';

Q : Surveiller l’état de suspension et les verrous acquis

Vous pouvez utiliser les vues de gestion dynamique (DMV) suivantes :

• sys.dm_server_suspend_status (afficher l’état de suspension)
• sys.dm_tran_locks (afficher les verrous acquis)

R. Répertorier les détails du jeu de sauvegarde

L’exemple de script suivant répertorie les informations du jeu de sauvegarde pour les sauvegardes d’instantanés Transact-SQL.

SELECT database_name,
    type,
    backup_size,
    backup_start_date,
    backup_finish_date,
    is_snapshot
FROM msdb.dbo.backupset
WHERE is_snapshot = 1;

S. Vérifier si une base de données a été suspendue pour la sauvegarde d’instantané

L’exemple de script suivant génère des propriétés de niveau base de données pour les bases de données suspendues pour la sauvegarde d’instantané.

SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup');

T. Exemple de script de résolution des problèmes T-SQL

L’exemple de script suivant détecte les bases de données suspendues sur le serveur et les réactive 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

Contenu connexe

• backupmediaset (Transact-SQL)
• BACKUP (Transact-SQL)