Simular un backup comprimido en SQL Server

Públicado en por

Hola a todos, hace tiempo hicimos esta entrada. Con el paso del tiempo, hemos conseguido mejorar la query y obtener datos más completos. Vamos a explicarte cómo simular un backup comprimido en SQL Server.

¿Por qué es útil simular un backup comprimido?

Una de las tareas más recurrentes de un DBA es revisar la ejecución de los backups, así como planificarlos. Para esto último, esta query que os vamos a mostrar es de gran ayuda.

Estos son los datos que obtenemos con la query:

  • Tamaño de backup sin comprimir por BBDD y en total
  • Tamaño de backup comprimido por BBDD y en total
  • Ratio de compresión por BBDD y en total
  • Tiempo en segundos por BBDD y en total
  • Rendimiento en MB por segundo por BBDD y en total

A continuación os dejamos un ejemplo de la salida:

simular un backup comprimido en SQL Server

Gracias a estos datos poder saber cuánto va a tardar, y cuánto va a ocupar comprimido y sin comprimir. Lo que hace realmente el script es un backup a «NUL» (no confudir con NULL) con COPY_ONLY. Al hacer un backup a NUL, como comentábamos en la otra entrada, realiza el backup, obtenemos tiempo de lo que va a tardar y su rendimiento, etc. Pero con la ventaja de que no es necesario ocupar espacio.

Adicionalmente, al usar la opción de COPY_ONLY, no rompería el pan de copias en caso de que tenga modo de recuperación FULL la base de datos. Es un backup aparte.

Es especialmente útil cuando es un entorno nuevo y no sabes cuánto va a ocupar el espacio en disco ni lo que va a tardar. Con estos datos ya puedes solicitar el disco con el tamaño adecuado para las copias.

¿Qué ventajas ofrece sobre el antiguo script?

Una vez que tenemos claro qué hace el script, veamos qué ventajas tiene, y cuál elegir en cada caso.

  • Aunque se podría adaptar a un procedimiento almacenado, no es estrictamente necesario como el script antiguo
  • Obtenemos los datos de todas las bases de datos de usuario, en vez de solo una en concreto
  • Obtenemos el datos del tamaño comprimido sin calcularlo a través del ratio.

Si nuestro objetivo es hacer una estimación para una BBDD, puede ser recomendable hacerlo con el script antiguo.

A continuación, os dejamos el script:

USE master;
GO
SET NOCOUNT ON;
DECLARE @NullDevice NVARCHAR(50) =
    CASE WHEN CAST(SERVERPROPERTY('HostPlatform') AS NVARCHAR(100)) = 'Linux'
         THEN '/dev/null'
         ELSE 'NUL'
    END;
PRINT CONCAT('Dispositivo nulo detectado: ', @NullDevice);

IF OBJECT_ID('tempdb..#DBS') IS NOT NULL DROP TABLE #DBS;
CREATE TABLE #DBS (DBName SYSNAME PRIMARY KEY);

INSERT INTO #DBS(DBName)
SELECT name
FROM sys.databases
WHERE database_id > 4              -- Excluye master, tempdb, model, msdb
  AND state = 0                    -- ONLINE
  AND is_read_only = 0;            -- opcional: evita Read Only

IF OBJECT_ID('tempdb..#DBBackupSizeResults') IS NOT NULL DROP TABLE #DBBackupSizeResults;
CREATE TABLE #DBBackupSizeResults
(
    database_name               SYSNAME,
    backup_finish_date          DATETIME,
    backup_MB                   DECIMAL(18,1),
    backup_comprimido_MB        DECIMAL(18,1),
    ratio_compresion_pct        DECIMAL(5,1),
    tiempo_segundos             DECIMAL(18,3),
    rendimiento_MB_por_seg      DECIMAL(18,3),
    estado                      NVARCHAR(30),
    mensaje                     NVARCHAR(4000)
);

DECLARE @DB SYSNAME;

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT DBName FROM #DBS ORDER BY DBName;

OPEN cur;
FETCH NEXT FROM cur INTO @DB;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT CONCAT('--- Iniciando backup simulado de: ', @DB, ' ---');

    DECLARE @sql NVARCHAR(MAX);
    DECLARE @t0 DATETIME2(3) = SYSDATETIME();
    DECLARE @t1 DATETIME2(3);
    DECLARE @elapsed_s DECIMAL(18,3);
    DECLARE @backup_finish DATETIME;
    DECLARE @backup_MB DECIMAL(18,1);
    DECLARE @compressed_MB DECIMAL(18,1);
    DECLARE @ratio_pct DECIMAL(5,1);
    DECLARE @throughput DECIMAL(18,3);

    BEGIN TRY
        SET @sql = N'
            BACKUP DATABASE ' + QUOTENAME(@DB) + N'
              TO DISK = @NullDev
              WITH COPY_ONLY, COMPRESSION, CHECKSUM, STATS = 10;
        ';
        EXEC sys.sp_executesql @sql, N'@NullDev NVARCHAR(50)', @NullDev = @NullDevice;

        SET @t1 = SYSDATETIME();
        SET @elapsed_s = DATEDIFF_BIG(MILLISECOND, @t0, @t1) / 1000.0;

        -- Recupera los tamaños desde msdb del último backup FULL de esa BD
        ;WITH ult AS
        (
            SELECT TOP (1)
                   bs.backup_finish_date,
                   bs.backup_size / 1048576.0        AS backup_MB,
                   bs.compressed_backup_size / 1048576.0 AS compressed_MB
            FROM msdb.dbo.backupset AS bs
            WHERE bs.database_name = @DB
              AND bs.type = 'D'          -- FULL
            ORDER BY bs.backup_finish_date DESC
        )
        SELECT @backup_finish = backup_finish_date,
               @backup_MB = CAST(backup_MB AS DECIMAL(18,1)),
               @compressed_MB = CAST(compressed_MB AS DECIMAL(18,1))
        FROM ult;

        SET @ratio_pct = CASE WHEN @backup_MB > 0 THEN CAST(@compressed_MB * 100.0 / @backup_MB AS DECIMAL(5,1)) END;
        SET @throughput = CASE WHEN @elapsed_s > 0 THEN CAST(@compressed_MB / @elapsed_s AS DECIMAL(18,3)) END;

        INSERT INTO #DBBackupSizeResults
        (
            database_name, backup_finish_date, backup_MB, backup_comprimido_MB,
            ratio_compresion_pct, tiempo_segundos, rendimiento_MB_por_seg, estado, mensaje
        )
        VALUES
        (
            @DB, @backup_finish, @backup_MB, @compressed_MB,
            @ratio_pct, @elapsed_s, @throughput, N'OK', N'Backup simulado completado'
        );

        PRINT CONCAT('Finalizado ', @DB, ' | Comprimido ~ ',
                     ISNULL(CONVERT(VARCHAR(32), @compressed_MB), 'NULL'), ' MB',
                     ' | Tiempo ', ISNULL(CONVERT(VARCHAR(32), @elapsed_s), 'NULL'), ' s',
                     ' | Rendimiento ~ ', ISNULL(CONVERT(VARCHAR(32), @throughput), 'NULL'), ' MB/s');
    END TRY
    BEGIN CATCH
        INSERT INTO #DBBackupSizeResults
        (
            database_name, backup_finish_date, backup_MB, backup_comprimido_MB,
            ratio_compresion_pct, tiempo_segundos, rendimiento_MB_por_seg, estado, mensaje
        )
        VALUES
        (
            @DB, NULL, NULL, NULL, NULL, NULL, NULL,
            N'ERROR',
            CONCAT('Error ', ERROR_NUMBER(), ': ', ERROR_MESSAGE())
        );

        PRINT CONCAT('Error en ', @DB, ': ', ERROR_MESSAGE());
    END CATCH;

    FETCH NEXT FROM cur INTO @DB;
END

CLOSE cur;
DEALLOCATE cur;

PRINT '--- Resumen final ---';
SELECT
    database_name,   
    backup_MB as backup_sin_comprimir_MB,
    backup_comprimido_MB,
    ratio_compresion_pct,
    tiempo_segundos,
    rendimiento_MB_por_seg,
    backup_finish_date,
    estado,
    mensaje
FROM #DBBackupSizeResults
ORDER BY database_name;

SET NOCOUNT OFF;
PRINT 'Proceso completado.';
GO
-- Suma global de tamaños, tiempo total y rendimiento medio
PRINT '--- Totales estimados ---';
SELECT 
    COUNT(*) AS BBDD_procesadas_OK,
    CAST(SUM(backup_MB) AS DECIMAL(18,2)) AS total_backup_sin_comprimir_MB,
    CAST(SUM(backup_comprimido_MB) AS DECIMAL(18,2)) AS total_backup_comprimido_MB,
    CAST(100.0 * SUM(backup_comprimido_MB) / NULLIF(SUM(backup_MB), 0) AS DECIMAL(5,2)) AS ratio_compresion_total_pct,
    CAST(SUM(tiempo_segundos) AS DECIMAL(18,2)) AS tiempo_total_segundos,
    CAST(SUM(backup_comprimido_MB) / NULLIF(SUM(tiempo_segundos), 0) AS DECIMAL(18,2)) AS rendimiento_MB_por_seg_global
FROM #DBBackupSizeResults
WHERE estado = 'OK';

Esperamos que os sea de utilidad. Si queréis que revisemos vuestro plan de backup o tenéis algún tipo de problema o consulta con SQL Server, no dudes en contactarnos. Somos expertos en SQL Server.

¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.

Sígue a GPS en LinkedIn

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *