Introduction
In this tutorial I will show how to archive all backup (Full, Diff, Log) in a single archive file using 7zip.
Background
Few days ago I have implemented MaintenanceSolution.sql for my backup purpose which is developed by Mr. Ola Hallengren.
This process creates three folder FULL, Diff, LOG for their respected files. It creates a big problem for me to collect last days backup
from different path. One full, six diff, and twenty four log backup need to be copied. So I wanted to archive all files in one file so I can easily copy them for remote backup.
What you Need
This script was tested in SQL Server 2008.
Download 7-Zip Command Line Version for Windows from http://downloads.sourceforge.net/sevenzip/7za920.zip
Main Script
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)
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';
set @ZipPath='D:\Backup\7za.exe';
set @query=null;
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
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
After Executing the Script
After executing the script probably you will get an error message like this:
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.
To resolve this issue you need to do either by 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
or GUI (SQL 2005 only):
- Open the “Microsoft SQL Server 2005” program group.
- Open the “Configuration Tools” subgroup.
- Start the “SQL Server Surface Area Configuration” tool.
- At the bottom of the SQL Server 2005 Surface Area Configuration task menu select: “Surface Area Configuration for Features”.
- Expand “MSSQLSERVER” and then expand “Database Engine” if needed.
- Select “xp_cmdshell” and select the check box to “Enable xp_cmdshell” and finally click “OK” and close the window.
Analysis Script
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
This will retrieve the physical backup file name and insert them in a table for further processing. If you want to include only last 24 hours backup then add:
AND (convert(varchar,backup_finish_date,101) = convert(varchar,DATEADD(day, - 1, GETDATE()),101))
Analysis Script
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
The tempBackup table is holding all backup list then execute 7za.exe from command prompt by xp_cmdshell.
7za.exe — This is the standalone version of the command line program. It only supports certain built-in compression formats (7z, zip, gzip, bzip2, Z and tar).
7za.exe doesn’t depend on any other files besides the EXE itself. So, this version is particularly handy for carrying around on a USB drive,
or for any other need where it is nice to only have to worry about the one EXE file being in place.
Here are the seven commands that are available in 7-Zip:
Command Description
- a: Add - create a new archive, or add files to an existing archive
- d: Delete - remove files from an existing archive
- e: Extract - unarchive files
- l: List - display the contents of an archive
- t: Test - validate the integrity of an archive
- u: Update - overwrite existing files in an existing archive
- x: Extract - same as “e”, except that the files are restored to their exact original locations (if possible)
References
History
None so far.
