Web design and hosting, database, cloud and social media solutions that deliver real business results
  • Business Solutions
    • Robotic Process Automation
    • Bespoke Software
    • Database Services
      • Data Integration
      • Datawarehouse Services
      • Power BI
    • Web Services
      • Logo Design
      • Payment Gateways
      • Web Site Optimisation
      • Web Site Security
      • Technical Tools
    • Cloud Services
      • Amazon Web Services
      • Google Cloud Services
      • Microsoft Azure
    • Microsoft Office
    • Social Media Services
  • Academy
    • Our Test Environment
    • Learning Databases
      • The Basics
      • Get Open Query
      • SQL Server Data
      • SQL Server Maintenance
      • Using SQL Server Dates
      • Using SQL Server Functions
      • Using SQL Server Pivot-Unpivot
      • Technical Tools
    • Learning Web Design
      • Building Ousia Content Management System
      • Using ASP-NET
      • Using CSS
      • Using JavaScript
    • Learning Cloud and IT Services
      • Task Scheduler Error 2147943645
      • Requesting SSL and Generation of PFX file in OpenSSL Simple Steps
    • Using Social Media
      • Asking for a Google Review
      • Changing a Facebook account from personal to business
      • Choosing where to focus Social Media effort
      • Social Media Image Sizes
      • Using Meta Data to set Social Media Images
  • About
    • Blog
      • Building an entry level gaming machine
      • Google Core Update Jan 2020
      • Hot Chilli Internet Closure
      • How To Choose Content For Your Website Adverts Leaflets
      • Preventing Online Scam
      • Skimmers of the gig economy
      • The most annoying things about websites on the Internet
      • Top 5 websites for free Vector Graphics
    • Portfolio
    • Team
      • Adrian Anandan
      • Ali Al Amine
      • Ayse Hur
      • Chester Copperpot
      • Fernando Ancona Camara
      • Gavin Clayton
      • Sai Gangu
      • Suneel Kumar
      • Surya Mukkamala
      • Trevor Momanyi

SQL Server 2008 7Zip Database Backup

SQL Code to enable xp_cmdshell and then run a database backup backup through the 7Zip command line.

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 BEGIN
IF
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 @Command
END
END
GO
EXEC
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.

Database Backup Sizes

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

Author

Gavin Clayton
Gavin Clayton
I formed Claytabase in 2010 as a way of carrying on my work with SQL Server and ASP.NET. This has culminated in the Ousia Content Management System being taken from concept to one of the quickest CMS's on the market.

Was this helpful?

Please note, this commenting system is still in final testing.
Copyright Claytabase Ltd 2021, registered in England and Wales 08985867

Site Links

RSS Login ContactCookie PolicySitemap

Social Media

facebook.com/Claytabaseinstagram.com/claytabase/twitter.com/Claytabaselinkedin.com/company/claytabase-ltd

Get in Touch

+442392064871info@claytabase.co.ukGround Floor, Building 1000, Lakeside North Harbour, Western Road, Portsmouth, Hampshire, United Kingdom, PO6 3EZ

Partnered With

Partners
The settings on this site are set to allow all cookies. These can be changed on our Cookie Policy & Settings page.
By continuing to use this site you agree to the use of cookies.
Ousia Logo
Ousia CMS Loader