SQL Server 2008 7Zip データベースバックアップ

xp_cmdshell を有効にしてから、7Zip コマンドラインからデータベースバックアップバックアップを実行する SQL コード。

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 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

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...