动态归档 SQL Server 备份（完整、差异、日志）
如何使用 7zip 将所有备份（完整、差异、日志）归档到一个文件中。
引言
在本教程中，我将演示如何使用 7zip 将所有备份（完整、差异、日志）归档到一个文件中。
背景
几天前，我为我的备份目的实施了 Mr. Ola Hallengren 开发的 MaintenanceSolution.sql。此过程为各自的文件创建了三个文件夹 FULL、Diff 和 LOG。这对我来说收集过去几天的备份变得非常麻烦，需要从不同的路径复制一个完整备份、六个差异备份和二十四个日志备份。因此，我希望将所有文件归档到一个文件中，以便可以轻松地将其复制到远程备份。
你需要的东西
此脚本在 SQL Server 2008 中进行了测试。
从 http://downloads.sourceforge.net/sevenzip/7za920.zip 下载 7-Zip 命令行版本 for Windows。
主脚本
----------------------------------------------------------------------------
-- Variable
----------------------------------------------------------------------------
DECLARE @Count INT
DECLARE @query nvarchar(4000)
DECLARE @current INT
DECLARE @name VARCHAR(2000)
DECLARE @ArchiveName VARCHAR(2000)
declare @DestinationPath varchar(500)
declare @ZipPath varchar(500)
DECLARE @tempBackup TABLE
(
intID INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(2000),
backup_start_date datetime,
backup_finish_date datetime,
backup_size_mb varchar(20),
backup_type varchar (50)
)
declare @sqlcmd varchar(200)
declare @BackupDate varchar(50)
----------------------------------------------------------------------------
-- Initial Value Assign
----------------------------------------------------------------------------
-- Get Last Date
select @BackupDate=convert(varchar,DATEADD(day, - 1, GETDATE()),101);
set @BackupDate=REPLACE(@BackupDate, '/','');
set @DestinationPath='D:\Backup\MASUD-PC';
set @ArchiveName=@DestinationPath+'Backup_'+ @BackupDate+'.zip'; -- file name format
set @ZipPath='D:\Backup\7za.exe'; -- 7zip file path
set @query=null;
----------------------------------------------------------------------------
-- Load Backup list
----------------------------------------------------------------------------
INSERT INTO @tempBackup(name,backup_start_date,backup_finish_date,backup_size_mb,backup_type)
SELECT physical_device_name,backup_start_date,
backup_finish_date,
convert(varchar,cast(backup_size/1024/1024 as money),10),
CASE [Type]
WHEN 'D' THEN 'Full Backups'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'Filegroup'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
WHEN 'V' THEN 'Verifyonly'
END AS rhType
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m
ON b.media_set_id = m.media_set_id
WHERE database_name IN ('AdventureWorks')
AND (convert(varchar,backup_finish_date,101) = convert(varchar,DATEADD(day, - 1, GETDATE()),101))
ORDER BY backup_finish_date DESC
----------------------------------------------------------------------------
-- Archive
----------------------------------------------------------------------------
SELECT @Count = COUNT(intID) FROM @tempBackup
IF ((@Count IS NOT NULL) AND (@Count > 0))
BEGIN
set @current=1
WHILE (@current <= @Count)
BEGIN
BEGIN TRY
SELECT @name = name FROM @tempBackup WHERE intID = @current
set @query=@query+ @name ;
SET @SQLCmd = @ZipPath+ ' a -tzip '+@ArchiveName+' '+@name;
EXEC master..xp_cmdshell @SQLCmd
END TRY
BEGIN CATCH
END CATCH
set @current=@current+1
END
END
执行脚本后
执行脚本后，你可能会收到如下错误消息：
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell'
because this component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'xp_cmdshell' by using sp_configure.
For more information about enabling 'xp_cmdshell',
see "Surface Area Configuration" in SQL Server Books Online.
要解决此问题，你可以通过 T-SQL 执行以下操作：
USE [master]
EXECUTE SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE SP_CONFIGURE 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
EXECUTE SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
或通过 GUI（仅限 SQL 2005）：
- 打开“Microsoft SQL Server 2005”程序组。
- 打开“配置工具”子组。
- 启动“SQL Server Surface Area Configuration”工具。
- 在 SQL Server 2005 Surface Area Configuration 任务菜单底部选择：“Surface Area Configuration for Features”。
- 展开“MSSQLSERVER”，然后在需要时展开“Database Engine”。
- 选择“xp_cmdshell”，选中“启用 xp_cmdshell”复选框，然后单击“确定”并关闭窗口。
分析脚本
INSERT INTO @tempBackupCheck(name,backup_start_date,backup_finish_date,backup_size_mb,backup_type)
SELECT physical_device_name,backup_start_date,
backup_finish_date,
convert(varchar,cast(backup_size/1024/1024 as money),10),
CASE [Type]
WHEN 'D' THEN 'Full Backups'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'Filegroup'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
WHEN 'V' THEN 'Verifyonly'
END AS rhType
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m
ON b.media_set_id = m.media_set_id
WHERE database_name IN ('AdventureWorks')
AND (convert(varchar,backup_finish_date,101) = convert(varchar,DATEADD(day, - 1, GETDATE()),101))
ORDER BY backup_finish_date DESC
这将检索物理备份文件名并将它们插入到表中以供进一步处理。如果你只想包含过去 24 小时的备份，则添加：
AND (convert(varchar,backup_finish_date,101) = convert(varchar,DATEADD(day, - 1, GETDATE()),101))
分析脚本
SELECT @Count = COUNT(intID) FROM @tempBackup
IF ((@Count IS NOT NULL) AND (@Count > 0))
BEGIN
set @current=1
WHILE (@current <= @Count)
BEGIN
BEGIN TRY
SELECT @name = name FROM @tempBackup WHERE intID = @current
set @query=@query+ @name ;
SET @SQLCmd = @ZipPath+ ' a -tzip '+@ArchiveName+' '+@name;
EXEC master..xp_cmdshell @SQLCmd
END TRY
BEGIN CATCH
END CATCH
set @current=@current+1
END
END
tempBackup 表保存所有备份列表，然后通过 xp_cmdshell 从命令行执行 7za.exe。
7za.exe —— 这是命令行程序的独立版本。它仅支持某些内置压缩格式（7z、zip、gzip、bzip2、Z 和 tar）。7za.exe 不依赖于任何其他文件，除了 EXE 文件本身。因此，此版本特别适合携带在 USB 驱动器上，或者在任何只需要 EXE 文件在原位的需求中使用。
7-Zip 中有七个可用的命令：
命令描述
- a：添加 - 创建新的归档文件，或将文件添加到现有归档文件
- d：删除 - 从现有归档文件中删除文件
- e：提取 - 解压缩文件
- l：列出 - 显示归档文件的内容
- t：测试 - 验证归档文件的完整性
- u：更新 - 覆盖现有归档文件中的现有文件
- x：提取 - 与“e”相同，但文件会恢复到其原始位置（如果可能）
参考文献
历史
目前还没有。