Zip/Compress SQL Backup on Windows 2008R2 server

Posted on 2012-08-10
Last Modified: 2012-08-13
I am trying to determine the best/quickest way to backup, zip and move a SQL database backup file across a network.  

I'm trying to copy a large file across the network.  I would like to speed it up by compressing it or speeding it up some other way.

The source system is using Windows Server 2008R2 and SQL Server 2008 Standard Edition.  The destination system is using Windows Server 2008R2 and SQL Server 2008R2 but I will be moving to SQL 2012 Standard Edition in the next few weeks.

Specifically, I want to copy the .BAK file (already backup using a SQL agent job), zip/compress that file, copy it across the network (a VPN) to the destination server and unzip it.  Currently the db.bak file is about 16 GB, but it will grow.  I'm wondering if Windows Server 2008R2 has a way to zip a file that large or if powershell can?  I don't know the first thing about powershell.  I tried this a long time ago and as I recall the server I was using at the time could not handle a (zip) file that large.  Or, do I have to use something like WinRAR?  It is important that I automate the process.  I don't want to do it manually each day (or weekend).

Thanks for your help and ideas.
Question by:CalBob
    LVL 12

    Accepted Solution

    Since this is SQL 2008 (R2), you can use the built-in backup need for ZIP or RAR utility.

    Just add "WITH COMPRESSION" to the backup statement.

    Author Comment

    Thanks.  I was trying to make is more complicated.  I haven't used backup compression before so it didn't occur to me.

    Author Comment

    I've requested that this question be closed as follows:

    Accepted answer: 0 points for CalBob's comment #a38282654

    for the following reason:

    Best answer.
    LVL 12

    Expert Comment

    It seems to me I supplied the answer....the author says "thanks" but wants to close it without awarding points??
    LVL 7

    Expert Comment

    Just a quick point to add,

    If you're sending this over a WAN you're better off breaking it up into multiple files and sending them in parallel OR using a robocopy type program and giving it multiple threads.  I realized the power of this method when I had to copy backups from Singapore to Downtown LA and the latency was killing me.

    Author Closing Comment

    This was the answer I meant to accept.  I must have clicked incorrectly.  Thanks again.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Some time ago I faced the need to use a uniform folder structure that spanned across numerous sites of an enterprise to be used as a common repository for the Software packages of the Configuration Manager 2007 infrastructure. Because the procedu…
    To effectively work with Diskpart on a Server Core, it is necessary to write some small batch script's, because you can't execute diskpart in a remote powershell session. To get startet, place the Diskpart batch script's into a share on your loca…
    This tutorial will give a short introduction and overview of Backup Exec 2014 and the additional features that have been added over its predecessor Backup Exec 2012. As with Backup Exec 2012, the Backup Exec button in the upper left corner. From her…
    This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now