Backup to Device (tape) - Hardware Compression?

Posted on 2004-11-30
Last Modified: 2010-08-05
Hi Guys,

We have an SQL Server 2K database that was approximately 198GB.
- This was backed up to an LTO1 tape drive (Dell PowerVault 110T) and fit on 1 tape - Done through SQL Enterprise Manager.

The database was reaching the full capacity of LTO1 tapes. We upgraded to an LTO2 drive (Dell PowerVault 122T Auto-loader) which has twice the capacity. (200gb native, 400gb compressed)

The database is now at around 203GB, and when we go to do a backup through Enterprise Manager the data has to be spanned across two LTO2 tapes. Im wondering why this is happening and can only guess that there is no hardware compression taking place?

It should easily fit onto 1 LTO2 tape, considering the database is only 5gb larger than when we were using LTO1. With the LTO2 it would get about 98% of the way thorugh the backup before asking for another tape.

I have spoken to Dell who could not recommend anything other than installing BackupExec SQL Agent..They also told me there are no options on the device itself to set any sort of configuration for this.

I know this is a bit borderline Hardware/SQL but i thought this would be a good place to start.

500 points for this one, its rather urgent to us
Question by:aaron-
    LVL 16

    Expert Comment

    Right, my understanding is that SQL Server BACKUP automatically uses hardware compression if the tape device supports it. Dell's site confirms that the backup software is responsible for setting whether or not hardward compression is enabled. The only thing I can think you might check is this:


    If this trace flag is on, it specifically disabled the use of hardward compression by BACKUP. It's a long shot, as I would think you'd know this was set if it was, but worth checking. If it is on, then:

    DBCC TRACEOFF (3205)

    will clear it.

    Author Comment

    Getting this when attepting the TRACESTATUS:

    Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Im not a real database guru so excuse my ignorance..
    LVL 16

    Accepted Solution

    OK - just checked, you can't use DBCC TRACESTATUS until you've used a DBCC TRACEON command first. Run these two:

    DBCC TRACEON(3205)



    See if this sorts out the compression problem. Like I said, it's sort of a long shot, but the only setting I can think of that might allow you to do this with SQL backup.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    755 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

    21 Experts available now in Live!

    Get 1:1 Help Now