Backup Compression Restrictions

Question

Backup Compression Restrictions

rollercoaster43

Ten Centuries

Points: 1306
More actions
April 8, 2014 at 7:00 am

#292070

I came across the below Backup Compression restrictions. can someone please add on any more restrictions to backup compression in Sql 2012 or other supported versions that I may be missing?
1 Compressed and uncompressed backups cannot co-exist in a media set.
2 Previous versions of SQL Server cannot read compressed backups.
3 NTbackups cannot share a tape with compressed SQL Server backups
Thanks..!

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

rollercoaster43 (4/8/2014)
2 Previous versions of SQL Server cannot read compressed backups.

This one isn't really a restriction. You can never restore down-version anyway, no matter whether the backup is compressed or not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 2

Not so sure about #3. Why not? After SQL Server does the compression, it's just a file.

One other one though, prior to 2014, you can't encrypt and compress using native tools.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

george sibbald SSC Guru Points: 104210 More actions · Answer 3

backup compression is not compatible with TDE.

you can enable it but will get very little if any compression.

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

rollercoaster43 Ten Centuries Points: 1306 More actions · Answer 4

Thanks Gail.. You are right.. This point in invalid since there is no backward compatibility for restore operations in sql server..

Thanks George for pointing that out..

Hi Grant,

I am trying to get a new set up ready to check if this is really a problem since even I feel this should not be one.

Just wanted to check if anyone has already tried this so that I can be rest assured this limitation does not exist..

Thanks...

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 5

I've put SQL Server compressed backups with non-compressed backups, script files, all sorts of things. It's just a file. I can't tell you that I have specifically put it on a tape sitting immediately next to an NT backup though. But there's nothing special about that I'm aware of.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning