Copia de seguridad de bases de datos SQL Server 2008 7Zip

Código SQL para habilitar xp_cmdshell y luego ejecutar una copia de seguridad de base de datos a través de la línea de comandos 7Zip.

Contexto

Recientemente me encontré con la necesidad de copiar por FTP las copias de seguridad de la base de datos fuera del lugar. Aunque obviamente se puede hacer esto con archivos de copia de seguridad estándar, los archivos de gran tamaño tardarían bastante tiempo, así que nos fijamos en 7 zip como una herramienta para comprimir aún más los datos, ya que la base de datos está siempre en línea, y la compresión incorporada afecta bastante al rendimiento.

7 zip se ejecutaría a través de la utilidad xp_cmdshell, que está deshabilitada por defecto. Para configurarla, utiliza los siguientes comandos a través de sp_configure. Este fragmento se ha extraído del sitio web de Microsoft.

SQL

-- To allow advanced options to be changed.EXEC sp_configure 'show advanced options', 1;GO-- To update the currently configured value for advanced options.RECONFIGURE;GO-- To enable the feature.EXEC sp_configure 'xp_cmdshell', 1;GO-- To update the currently configured value for this feature.RECONFIGURE;

¿Fue útil?

Ahora que la hemos activado, podemos configurar el siguiente procedimiento almacenado, este código podría utilizarse como parte del plan de mantenimiento completo (copia de seguridad, restauración y reindexación).

SQL

CREATE PROC dbo.DatabaseBackup(@Database NVARCHAR(100),@Folder NVARCHAR(100),@DatabaseRAR BIT)AS BEGINIF RIGHT(@Folder,1)<>'\' SET @Folder=@Folder+'\'DECLARE @Source VARCHAR(1000)=@Folder+@Database+'.bak'DECLARE @Destin VARCHAR(1000)=@Folder+@Database+'.rar'DECLARE @Command VARCHAR(1000),@CommandDel VARCHAR(1000)
DECLARE @BackupScript NVARCHAR(1000)='BACKUP DATABASE '+@Database+' TO DISK=N'''+@Source+'''WITH NOFORMAT, INIT, NAME =N'''+@Database+'_Backup'', SKIP, NOREWIND, NOUNLOAD, STATS= 10;'EXEC (@BackupScript)
IF @DatabaseRAR=1 BEGIN  SET @CommandDel = N'del '+@Destin+''  SET @Command = N'"C:\Program Files\7-Zip\7z.exe" a '+@Destin+' '+@Source+' '  PRINT @Command  EXEC xp_cmdshell @CommandDel  EXEC xp_cmdshell @CommandENDENDGOEXEC DatabaseBackup 'db','C:\\',1

¿Fue útil?

Una vez configurado, una copia de seguridad de la base de datos de 20 GB tardó 7 minutos, y comprimirla poco más de una hora. Si es posible, se recomienda hacer esto en un servidor separado o en unidades separadas de sus bases de datos para evitar la contención de IO de disco.

Aquí hay una captura de pantalla de las tasas de compresión de la copia de seguridad en una base de datos, donde se puede ver el archivo .rar es aproximadamente un tercio del tamaño de la copia de seguridad comprimida, y la copia de seguridad comprimida es un poco más de la mitad del tamaño de la no comprimida.

El FTP se hace por separado en la actualidad, pero en su momento voy a construir en el FTP de este código...