Click here to Skip to main content
15,868,164 members
Articles / Database Development / SQL Server
Article

Archive all SQL Server backup (Full, Diff, Log) dynamically

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
4 Jan 2013BSD2 min read 26.4K   393   12   1
How to archive all backup (Full, Diff, Log) in a single archive file using 7zip.

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.

Image 1

Image 2

Image 3

Image 4

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

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

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:

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

  1. Open the “Microsoft SQL Server 2005” program group.
  2. Open the “Configuration Tools” subgroup.
  3. Start the “SQL Server Surface Area Configuration” tool.
  4. At the bottom of the SQL Server 2005 Surface Area Configuration task menu select: “Surface Area Configuration for Features”.
  5. Expand “MSSQLSERVER” and then expand “Database Engine” if needed.
  6. Select “xp_cmdshell” and select the check box to “Enable xp_cmdshell” and finally click “OK” and close the window.

Analysis Script

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

SQL
AND   (convert(varchar,backup_finish_date,101) = convert(varchar,DATEADD(day, - 1, GETDATE()),101))

Analysis Script

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

Image 5

References

History

None so far.

License

This article, along with any associated source code and files, is licensed under The BSD License


Written By
Software Developer (Senior) icddr,b
Bangladesh Bangladesh
More than 8 years experience on Programming and Project implementation, I was primarily involved with projects for private organization,Govt.(Bangladesh Army,DG Health,RJSC), NGO (SEDF,WFP). Presently I am working at ICDDR,B and enhancing Hospital Management System developed by Microsoft Dynamic NAV and Windows Mobile Application 5.0

An active supporter of Open Source technology, my interested areas are ERP, IT Audit, Data warehouse, BI etc.

Playing Guitar for 15 years, my interested music style is Blues Rock,Neo Classical.

Certification

70-540:Microsoft® Windows Mobile® 5.0 - Application Development
MB7-514:Microsoft Dynamics™ NAV 5.0 C/SIDE Introduction
MB7-516:Microsoft Dynamics™ NAV 5.0 Solution Development
MB7-517:Microsoft Dynamics™ NAV 5.0 Installation and Configuration
MB7-515:Microsoft Dynamics™ NAV 5.0 Financials
70-432:Microsoft SQL Server 2008 - Implementation and Maintenance
70-450:PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008
70-448:Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
312-50:Certified Ethical Hacker

Web :http://masudparvezshabuz.appspot.com
Blog :http://masudparvezshabuz.wordpress.com
linkedin :http://www.linkedin.com/in/masudparvez

Comments and Discussions

 
Questionhow to retrive data from zip file Pin
softprga25-Mar-13 21:30
softprga25-Mar-13 21:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.