December 12, 2014 at 9:57 am
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]
December 12, 2014 at 10:04 am
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
December 12, 2014 at 10:13 am
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]
December 12, 2014 at 10:23 am
Database isn't compressed already is it?
'Only he who wanders finds new paths'
December 12, 2014 at 10:37 am
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
December 12, 2014 at 10:38 am
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]
December 12, 2014 at 10:39 am
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]
December 12, 2014 at 10:48 am
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?
December 12, 2014 at 11:07 am
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]
December 12, 2014 at 11:19 am
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]
December 12, 2014 at 12:14 pm
yes, unfortunately TDE negates backup compression.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 12, 2014 at 5:03 pm
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