Post reply

Backup Compression Not Working on 2008 R2

  • August 21, 2012 at 10:25 am

    #265640

    We just noticed a SQL Server 2008 R2 development server running on Hyper-v is not using backup compression despite it being enabled sp_configure 'backup compression default', '1'

    Compression on the corresponding production server works as expected for all databases. Any idea what may be preventing compression from working?

    Of note, we can successfully backup a database and achieve compression if we redirect the backup file to a remote server that is not virtualized.

    Thanks, Dave

  • August 21, 2012 at 10:47 am

    #1528243

    did someone run RECONFIGURE after changing this?

  • August 21, 2012 at 10:57 am

    #1528251

    Are you sure the backups are actually setup to use compression? Just setting the default will not override the value in a maintenance plan if that value was specifically changed to not use compression.

    Also, what Edition of SQL Server are you using on your development server?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • August 21, 2012 at 11:13 am

    #1528263

    Same edition as the production server, which is Enterprise. The jobs are also identical. I know compression is enabled because I can compress the data to a remote location by specifying DISK = '\\RemoteServer\..."

    I just determined the failure was due to compression being enabled after backup jobs had been created and run at least once without compression, thereby creating a backup file. Despite the backup job containing a WITH INIT causing the file to be initialized, SQL Server was unable to compress the file once compression was enabled. This tells me there may be some file attribute or header that contains information being read by SQL Server, and telling SQL Server the file cannot be compressed. We deleted the backup file and re-ran the job with successful compression.

    Thanks

  • August 21, 2012 at 12:36 pm

    #1528322

    Check this, it's on the Restrictions part

    http://msdn.microsoft.com/en-us/library/bb964719(v=SQL.100).aspx

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 21, 2012 at 1:13 pm

    #1528344

    Are you referring to this bullet?

    Compressed and uncompressed backups cannot co-exist in a media set.

    I saw that, but interpreted it as meaning you should not add compressed backups to a media set containing an uncompressed backup. I thought since an INIT was used the previous backup would have been removed, which it was. The problem being the header still contained information related to uncompressed backups. It probably would have worked had I issued the FORMAT command to reformat the header. I'll run a test later today to confirm.

    Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply