SQL Server 2008 7Zip Database Backup

SQL Code to enable xp_cmdshell and then run a database backup backup through the 7Zip command line.

Context

I recently came across the need to FTP copy database backups off site, while you can obviously do this with standard backup files, large files would take quite some time, so we looked at 7 zip as a tool to further compress the data, as the database is always on-line, and the built in compression takes quite a hit on performance.

7 zip would be run via the xp_cmdshell utility which is disabled by default. to set it up, use the following commands via sp_configure. This snippet was taken from the Microsoft site.

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;

Now that we have turned that on, we can now set up the following stored procedure, this code could be utilised as part the complete maintenance plan (backup, restore and re-index).

SQL

CREATE PROC dbo.DatabaseBackup(@Database NVARCHAR(100),@Folder NVARCHAR(100),@DatabaseRAR BIT)

AS BEGIN

IF 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 @Command

END

END

GO

EXEC DatabaseBackup 'db','C:\\',1

When set up, a 20GB database backup took 7 minutes, and then to zip it took just over an hour. If possible, you would want this done on a separate server or at separate drives away from your databases to avoid Disk IO contention.

Here is a screenshot of the backup compression rates on a database, where you can see the .rar file is about a third the size of the compressed backup, and the compressed backup is just over half the size of the uncompressed one.

The FTP is done separately at present, but in due course I will be building in the FTP to this code...