Creación de una copia de seguridad de instantáneas de Transact-SQL

Se aplica a: SQL Server 2022 (16.x)

En este artículo se explica qué, por qué y cómo usar copias de seguridad de instantánea de Transact-SQL. Las copias de seguridad de instantáneas de Transact-SQL (T-SQL) se introdujeron en SQL Server 2022 (16.x).

Las bases de datos cada vez son más grandes. Tradicionalmente, las copias de seguridad de SQL Server son copias de seguridad de streaming. Una copia de seguridad de streaming depende del tamaño de la base de datos. Las operaciones de copia de seguridad consumen recursos (CPU, memoria, E/S, red) que afectan al rendimiento de la carga de trabajo OLTP simultánea durante la copia de seguridad. Una manera de hacer que el rendimiento de la copia de seguridad sea constante, en lugar de depender del tamaño de los datos, consiste en realizar una copia de seguridad de instantáneas mediante mecanismos proporcionados por el hardware o el servicio de almacenamiento subyacentes.

Dado que la propia copia de seguridad se produce en el nivel de hardware, no es una solución SQL Server pura. SQL Server primero debe preparar los archivos de datos y de registro para la instantánea para que se garantice que los archivos estén en un estado que se pueda restaurar más adelante. Una vez completado este paso, las operaciones de escritura se pausan en SQL Server (las solicitudes de lectura siguen estando permitidas), y el control pasa a la aplicación de copia de seguridad para completar la instantánea. Una vez completada correctamente la instantánea, la aplicación debe devolver el control a SQL Server donde se reanudan las operaciones de escritura.

Dado que debemos inmovilizar las operaciones de escritura durante la operación de instantánea, es esencial que la instantánea se produzca rápidamente, de modo que la carga de trabajo del servidor no se interrumpa durante un período prolongado. En el pasado, los usuarios dependían de soluciones de terceros que se crearon sobre el servicio del objeto de escritura de SQL para completar copias de seguridad de instantáneas. El servicio del objeto de escritura de SQL depende de VSS (Servicio de instantáneas de volumen) de Windows junto con VDI (Interfaz de dispositivo virtual) de SQL Server para realizar la orquestación entre SQL Server y la instantánea de nivel de disco.

Los clientes de copia de seguridad basados en el servicio del objeto de escritura de SQL tienden a ser complejos y solo funcionan en Windows. Con las copias de seguridad de instantáneas de T-SQL, el lado SQL Server de la orquestación se puede controlar con una serie de comandos de T-SQL. Esta funcionalidad permite a los usuarios crear sus propias aplicaciones de copia de seguridad pequeñas que se pueden ejecutar en Windows o Linux, o incluso soluciones con scripts si el almacenamiento subyacente admite una interfaz de scripting para iniciar una instantánea.

Este es un script de PowerShell de ejemplo que muestra una solución integral de copia de seguridad y restauración de una base de datos en una máquina virtual IaaS de Azure SQL. En el ejemplo se usan las funcionalidades de copia de seguridad de instantáneas de T-SQL introducidas en SQL Server 2022 (16.x).

Flujo de trabajo

La sintaxis de copia de seguridad de instantáneas de T-SQL desacopla el mecanismo de instantánea dependiente del proveedor de las operaciones de suspensión y copia de seguridad. Con esta sintaxis, puede:

1. Inmovilizar una base de datos con el comando ALTER, lo que le proporciona la oportunidad de realizar la instantánea del almacenamiento subyacente. Después, puede reanudar la base de datos y registrar la instantánea con el comando BACKUP.

2. Realizar instantáneas de varias bases de datos simultáneamente con los nuevos comandos BACKUP GROUP y BACKUP SERVER. Con esta opción, se pueden realizar instantáneas en la granularidad de instantáneas del almacenamiento subyacente y eliminar la necesidad de realizar una instantánea del mismo disco varias veces.

3. Realice copias de seguridad FULL y COPY_ONLY FULL. Estas copias de seguridad también se registran en msdb.

4. Realice una recuperación a un momento dado mediante copias de seguridad de registros realizadas con el enfoque de streaming normal después de la copia de seguridad FULL de la instantánea. También se admiten copias de seguridad diferenciales de streaming si lo desea.

En el diagrama siguiente se muestra el flujo de trabajo de alto nivel de las copias de seguridad de instantáneas de T-SQL:

El paso intermedio de instantánea requiere que inicie la instantánea en el almacenamiento subyacente. En el diagrama siguiente se muestra un ejemplo de cómo un script de copia de seguridad puede funcionar con SQL Server para completar el proceso de copia de seguridad de instantáneas:

Del mismo modo, un script de restauración podría funcionar de la siguiente manera:

Limitaciones

El número máximo de bases de datos de las que puede realizar una copia de seguridad con esta característica es 64. Si hay más de 64 bases de datos en el servidor, ve el siguiente error:

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.

Ejemplos

En las secciones siguientes se muestran distintos comandos de T-SQL usados para realizar la copia de seguridad de instantáneas en el disco. Cuando una copia de seguridad de instantáneas se escribe en el disco, solo se escriben en el archivo los metadatos conectados a la copia de seguridad de instantáneas. La salida no contiene ninguno de los contenidos de la base de datos, excepto el encabezado y el contenido del archivo. El archivo de shell creado como parte de la realización de la copia de seguridad de instantáneas debe usarse con el URI de instantánea real para realizar una copia de seguridad completa. Una RESTORE de una base de datos a partir de este archivo requiere que el usuario copie los archivos de base de datos desde el URI de la instantánea en el punto de montaje antes de emitir el comando RESTORE. Los usuarios pueden ejecutar todos los comandos tradicionales de T-SQL como RESTORE HEADERONLY y RESTORE FILELISTONLY en este archivo de metadatos de copia de seguridad de instantáneas junto con RESTORE DATABASE. La sintaxis admite la escritura de metadatos de copia de seguridad de instantáneas en DISK o URL. Los conjuntos de copia de seguridad de instantáneas también se pueden anexar igual que los conjuntos de copia de seguridad de streaming en un único archivo.

A Suspensión de una base de datos de usuario única para la copia de seguridad de instantáneas y registro de una copia de seguridad de base de datos

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

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

B. Suspensión de varias bases de datos de usuario para la copia de seguridad de instantáneas

Si hay varias bases de datos en el mismo disco subyacente, puede suspender varias bases de datos con el siguiente comando.

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. Suspender todas las bases de datos de usuario en el servidor para realizar una copia de seguridad de instantáneas

Si es necesario suspender todas las bases de datos de usuario del servidor, use el siguiente comando.

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

BACKUP SERVER
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

D. Suspensión de varias bases de datos de usuario con un único comando

Registre una instantánea de todas las bases de datos de usuario en el servidor en un único conjunto de copia de seguridad:

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. Realizar copias de seguridad de instantáneas de tipo Copy-Only

Dado que el mapa de bits diferencial se borra antes de la inmovilización, SUSPEND_FOR_SNAPSHOT_BACKUP proporciona una opción (COPY_ONLY) para no borrar el mapa de bits diferencial antes de la inmovilización.

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. Copia de seguridad de una base de datos con archivos de datos y de registro en unidades diferentes

Si tiene una base de datos con archivos de datos (.mdf y .ndf) en varias unidades y el archivo de registro de transacciones (.ldf) en una unidad diferente, puede realizar una copia de seguridad de instantáneas de la siguiente manera:

1. Suspenda la base de datos (que inmoviliza la E/S de escritura en los archivos de datos y de registro).

   ALTER SERVER CONFIGURATION
   SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
   

2. Tome una instantánea de todos los discos subyacentes en los que están presentes los archivos de registro y datos de la base de datos. Este paso depende del hardware.

3. Realice la copia de seguridad con la opción METADATA_ONLY, que crea la salida que contiene los metadatos de copia de seguridad de instantáneas (.bkm).

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

Para restaurar esta copia de seguridad en una fase posterior, siga estos pasos:

1. Monte o conecte los discos de instantáneas en la máquina virtual donde desea restaurar.

2. Use el archivo .bkm (del paso 3 de la lista anterior) al realizar una restauración de base de datos.

3. Si las unidades son diferentes durante la restauración, use la opción MOVE para que los archivos lógicos los coloquen en el destino necesario. Para ver un ejemplo, consulte ejemplo N.

G. Etiquetar el conjunto de copias de seguridad

Puede usar las opciones MEDIANAME y MEDIADESCRIPTION en el comando de copia de seguridad para etiquetar el URI asociado a la instantánea. Este uso permite que el archivo de copia de seguridad lleve la información de instantánea subyacente con los metadatos de la base de datos. También puede usar las opciones NAME y DESCRIPTION para etiquetar el URI con la instantánea individual del conjunto de copias de seguridad.

SQL Server no interpreta la información LABEL de ninguna manera. Pero ayuda al usuario a ver el URI asociado a la copia de seguridad de instantáneas con el comando RESTORE LABELONLY.

Después, podría conectar los discos de instantánea ubicados en el URI a la máquina virtual para restaurar la instantánea. El URI de instantánea almacenado en MEDIANAME y MEDIADESCRIPTION también está disponible para su visualización en la tabla de base de datos msdb dbo.backupmediaset.

H. Salida de la copia de seguridad de instantáneas con RESTORE HEADERONLY

La salida con RESTORE HEADERONLY es similar al siguiente ejemplo si la base de datos, el grupo y el servidor se ejecutan en secuencia y se escriben en el mismo archivo de salida:

RESTORE HEADERONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;

I. Salida de la copia de seguridad de instantáneas con RESTORE FILELISTONLY

La salida con RESTORE FILELISTONLY muestra el primer conjunto de copia de seguridad de manera predeterminada:

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;

J. Filtrar la salida de RESTORE FILELISTONLY a un conjunto de copia de seguridad

Para seleccionar específicamente un conjunto de copia de seguridad determinado de varios conjuntos de copia de seguridad con RESTORE FILELISTONLY, use la cláusula FILE que ya se admite en RESTORE FILELISTONLY.

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3;

K. Filtrar la salida de RESTORE FILELISTONLY a una base de datos

Para seleccionar una base de datos única de varias bases de datos dentro del conjunto de copia de seguridad seleccionado con RESTORE FILELISTONLY, use la cláusula FILE con la cláusula DBNAME recién introducida. La cláusula DBNAME solo se puede usar en conjuntos de copia de seguridad de instantáneas.

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3';

L. Restauración de una base de datos de instantáneas

Restaurar una base de datos a partir de una copia de seguridad de instantáneas es como adjuntar una base de datos. Ejecute el comando restore sin la opción RECOVERY si la base de datos debe adjuntarse sin recuperación. De forma predeterminada, RESTORE selecciona la primera base de datos del conjunto de copia de seguridad de instantáneas. En el ejemplo siguiente se restaura testdb1. Si testdb1 ya existe en el servidor, incluya la cláusula REPLACE. Debe montar los archivos de base de datos antes de ejecutar 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. Restauración de una base de datos de instantáneas que aparece en el medio

Si la base de datos que necesita RESTORED está en el medio, especifique la base de datos que se va a restaurar con la cláusula DBNAME. La sintaxis siguiente restaura la base de datos especificada en la cláusula 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;

Hora Restauración de la base de datos con un nombre distinto

Puede restaurar la base de datos con un nombre diferente. Si la base de datos que necesita RESTORED está en el medio, especifique la base de datos que se va a restaurar con la cláusula DBNAME. La siguiente sintaxis restaura la base de datos especificada con la cláusula DBNAME y cambia su nombre a 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. Usar RESTORE BACKUPSETONLY para extraer bases de datos de un conjunto de copia de seguridad que contiene varias bases de datos

Un conjunto de copia de seguridad de instantáneas que contiene varias bases de datos de un grupo o instantánea del servidor se puede dividir con el comando RESTORE BACKUPSETONLY. Este comando genera un conjunto de copia de seguridad por base de datos.

Si una instantánea del servidor contiene tres bases de datos en un archivo de copia de seguridad que contiene un único conjunto de copia de seguridad, el siguiente comando genera tres conjuntos de copia de seguridad, uno para cada base de datos. Crea un directorio con <file_name_prefix>_<unique_time_stamp> para los archivos de salida.

RESTORE BACKUPSETONLY
FROM DISK = 'D:\Temp\db1.bkm'
WITH METADATA_ONLY;

P. Usar RESTORE BACKUPSETONLY para extraer una base de datos específica de un conjunto de copia de seguridad que contiene varias bases de datos

RESTORE BACKUPSETONLY admite el parámetro DBNAME si el usuario quiere generar una base de datos de las tres bases de datos del conjunto de copia de seguridad. También admite el parámetro FILE para filtrar varios conjuntos de copia de seguridad en el archivo de copia de seguridad.

RESTORE BACKUPSETONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb2';

Q. Supervisar el estado de suspensión y los bloqueos adquiridos

Puede usar las vistas de administración dinámicas (DMV) siguientes:

• sys.dm_server_suspend_status (ver el estado de suspensión)
• sys.dm_tran_locks (ver los bloqueos adquiridos)

R. Enumerar los detalles del conjunto de copias de seguridad

En la siguiente secuencia de comandos de ejemplo se muestra la información del conjunto de copias de seguridad para las copias de seguridad de instantáneas de 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. Comprobación de si se suspendió una base de datos para la copia de seguridad de instantáneas

La siguiente secuencia de comandos de ejemplo genera propiedades de nivel de base de datos para las bases de datos suspendidas para la copia de seguridad de instantáneas.

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

T. Script de solución de problemas de T-SQL de muestra

La siguiente secuencia de comandos de ejemplo detecta bases de datos suspendidas en el servidor y no anular la suspensión si es necesario.

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

Contenido relacionado

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