Hola a todos, hoy vamos a hablaros de un sencillo truco que puede ser útil en varias situaciones. Se trata de simular un backup en SQL Server.
Os ponemos en situación. Recientemente, nos hemos encontrado con un problema trabajando. En una instancia de SQL Server, había varias bases de datos, de las que se hacía backup de algunas de ellas. Entre las bases de datos había una base de datos especialmente grande comparada con el resto. Necesitábamos:
- Saber el tamaño final del backup, tanto comprimido, como sin comprimir.
- Cuánto tiempo tardará en realizarse en cualquiera de las dos formas
- La velocidad en MB/s que ha obtenido.
Todo esto, en un escenario ideal en el que hay espacio, se puede simular con un backup with copy only para no interferir en las copias actuales. El problema que no disponemos de ese espacio, por lo que tuvimos que simular un backup en SQL Server.
¿Cómo simular un backup en SQL Server?
Una vez que sabíamos lo que teníamos que hacer, nos pusimos a investigar. La solución pasaba por hacer el backup a NUL. Recalcamos lo de NUL (con una L), no confundir con NULL (con dos L).
En primer lugar, veamos como sería un backup «habitual» a disco, sin comprimir:
Tras ejecutarlo nos devuelve la siguiente información:
Nos indica el tiempo que ha tardado: algo más de un segundo (1 segundo), la velocidad (casi 151 MB/s) y ha ocupado sin comprimir 207 MB:
Backup a disco con compresión
En el caso del backup comprimido, nos valdría la misma consulta anterior añadiéndole el parámetro «COMPRESSION».
BACKUP DATABASE AdventureWorks2019 TO DISK='C:\backup_sql\bkp_comprimido.bak' WITH INIT, STATS=5, COPY_ONLY, COMPRESSION
GO
Si lo comparamos con el backup anterior (sin comprimir). Todos los valores son mejores. Ha tardado menos tiempo y lo ha realizado más rápido. El tamaño también ha sido menor (de 207 MB a 48,2 MB):
Obtener ratio de compresión haciendo backup a NUL
Una vez que hemos visto como se hacen los backups «normales» a disco y las ventajas de la compresión sobre los mismos, vamos a ver cómo obtener el tamaño final del backup, obteniendo el ratio de compresión.
Para ello, lo primero vamos a agradecer el código compartido por Eli Leiba, el cual nos dio la solución perfecta. Por un lado podríamos saber el tamaño del backup sin realizar gracias al ratio de compresión. Y por otro lado, nos permitía ver cuanto se tardaba, de carga a una futura programación de tarea de backup. Todo ello sin disponer actualmente del espacio suficiente en disco.
Para que funcione, lo primero que hay que hacer es crear el procedimiento almacenado como indica en su página:
USE master
GO
-- ============================================================
-- Author: Eli Leiba
-- Create date: 12-2017
-- Description: Compute the DB backup compression ratio %
-- ============================================================
CREATE PROCEDURE usp_Calc_DB_Compression_Ratio_Pct (
@dbName SYSNAME,
@compressPct DECIMAL (5, 1) OUTPUT
)
AS
BEGIN
DECLARE @dynaTSQL VARCHAR(400)
SET NOCOUNT ON
SET @dynaTSQL = CONCAT (
'BACKUP DATABASE ',
@dbName,
' TO DISK = N',
'''',
'nul',
'''',
' with compression, copy_only '
)
EXEC (@dynaTSQL)
SELECT @compressPct = cast (100.0*a.compressed_backup_size / a.backup_size AS DECIMAL (5, 1))
FROM msdb..backupset a
WHERE lower (a.database_name) = @dbName AND a.backup_finish_date = (
SELECT max (backup_finish_date)
FROM msdb..backupset
)
SET NOCOUNT OFF
END
GO
Y una vez creado, ya se puede probar. Siguiendo el ejemplo anterior de AdventureWorks2019, en nuestro caso sería:
USE master GO DECLARE @comppct DECIMAL (5, 1) EXEC usp_Calc_DB_Compression_Ratio_Pct @dbname = 'AdventureWorks2019', @compressPct = @comppct OUTPUT PRINT @comppct
La salida sería parecía esta:
Processed 1184 pages for database 'Northwind', file 'Northwind' on file 1. Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 1. BACKUP DATABASE successfully processed 1186 pages in 0.312 seconds (29.674 MB/sec). 19.4
En este código, el ratio de compresión es del 19.4 %. Por lo que si el backup sin comprimir ocupara 100 MB exactos. El backup comprimido, ocuparía 19,4 MB. Y todo esto sin tener la necesidad (o la opción), de poder hacerlo directamente a disco por falta de espacio.
Si prefieres que nosotros nos encarguemos del backup, o quieres que valoremos tu entorno, consúltanos sin compromiso en nuestra página de contacto.
Si quieres estar al día de nuestras publicaciones apúntate a nuestra newsletter mensual:
Esperamos que estos trucos os hayan servido. Nos vemos en próximas entradas.