SQLSERVER 实现压缩zip文件

• SQL数据库压缩 7z

DECLARE @BakName VARCHAR(4000) 
DECLARE @FullPath VARCHAR(4000) 
DECLARE @CompressorFile VARCHAR(4000) 

SET @BakName = 'D:\Databases\BACKUP\testDateBase_' + CONVERT(CHAR(8),Getdate(),112) + '_' + REPLACE(CONVERT(CHAR(8),Getdate(),112),':','') 
SET @CompressorFile = @BakName + '.zip' + @BakName + '.bak' BACKUP database @dbname to disk=@Bak with init 
SET @FullPath = 'C:\Progra\7-Zip\7z.exe a ' + @CompressorFile + ' ' + @Bak 

exec master..xp_cmdshell @FullPath

• 用WinRAR压缩和解压文件
  –压缩文件: a 压缩，-ep 从名称中排除路径

declare @sql nvarchar(200)
set @sql = 'D:\software\WinRAR\WinRAR.exe a -ep F:\a.zip F:\a.txt'
exec master..xp_cmdshell @sql

注意： WinRAR.exe路径中不可以存在空格，存在会报错。如果解决没有调查过

启用和禁用xp_cmdshell
– 允许配置高级选项
EXEC sp_configure ‘show advanced options’,1

– 禁用xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, 1