Backup compression didn't work.

  • I took a full backup of the database yesterday and the backup file size was about 16GB. Then I backed up the same DB with compression since I was trying to save some space, but I don't think it worked since the backup file size is still 16GB. Any idea why that it.

    BACKUP DATABASE [DBNAME]

    TO

    DISK = 'C:\Directory'

    WITH COMPRESSION, FORMAT

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • What sort of data is in the database? If it's mainly text, the backup will compress well. If it's mainly LOB data, it won't.

    John

  • John Mitchell-245523 (12/12/2014)


    What sort of data is in the database? If it's mainly text, the backup will compress well. If it's mainly LOB data, it won't.

    John

    Well It's mostly text, we have some LOB data. I don't know the ratio, and I don't even know how to tell.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • Database isn't compressed already is it?

    'Only he who wanders finds new paths'

  • Comparing the backup_size and compressed_backup_size fields in msdb.dbo.backupset table should tell you how much compression, if any, took place.

    Which version & edition are you running? Backup compression on standard edition was only implemented with 2008 R2, not vanilla SQL 2008.

    Cheers

  • Gazareth (12/12/2014)


    Comparing the backup_size and compressed_backup_size fields in msdb.dbo.backupset table should tell you how much compression, if any, took place.

    Which version & edition are you running? Backup compression on standard edition was only implemented with 2008 R2, not vanilla SQL 2008.

    Cheers

    Running SQL Server 2008R2 Enterprise Edition.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • david.alcock (12/12/2014)


    Database isn't compressed already is it?

    No. Same size. Regular backup 16GB, backup after compression 16GB.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • New Born DBA (12/12/2014)


    Gazareth (12/12/2014)


    Comparing the backup_size and compressed_backup_size fields in msdb.dbo.backupset table should tell you how much compression, if any, took place.

    Which version & edition are you running? Backup compression on standard edition was only implemented with 2008 R2, not vanilla SQL 2008.

    Cheers

    Running SQL Server 2008R2 Enterprise Edition.

    *Entering clutching at straws mode*

    TDE enabled?

  • I got some information about the backup. It did compress some data, but not that much

    Backup size = 17250.93

    Compressed backup = 17206

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • Gazareth (12/12/2014)


    New Born DBA (12/12/2014)


    Gazareth (12/12/2014)


    Comparing the backup_size and compressed_backup_size fields in msdb.dbo.backupset table should tell you how much compression, if any, took place.

    Which version & edition are you running? Backup compression on standard edition was only implemented with 2008 R2, not vanilla SQL 2008.

    Cheers

    Running SQL Server 2008R2 Enterprise Edition.

    *Entering clutching at straws mode*

    TDE enabled?

    Yes TDE enabled. and I believe that using TDE and backup compression don't work together.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • yes, unfortunately TDE negates backup compression.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • New Born DBA (12/12/2014)


    Gazareth (12/12/2014)


    New Born DBA (12/12/2014)


    Gazareth (12/12/2014)


    Comparing the backup_size and compressed_backup_size fields in msdb.dbo.backupset table should tell you how much compression, if any, took place.

    Which version & edition are you running? Backup compression on standard edition was only implemented with 2008 R2, not vanilla SQL 2008.

    Cheers

    Running SQL Server 2008R2 Enterprise Edition.

    *Entering clutching at straws mode*

    TDE enabled?

    Yes TDE enabled. and I believe that using TDE and backup compression don't work together.

    I must clutch at straws well 🙂

Viewing 12 posts - 1 through 11 (of 11 total)

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