cancel
Showing results for 
Search instead for 
Did you mean: 

SQLCOMPRESSION TRUE on 7.0.1

davidbed
Level 3

Hello everyone,

Does anyone tried this new feature in 7.0.1?

I just tried this in test environment this morning and its working just fine, got 84% compression ratio and it took half the time to backup and restore.

Problem is, the DBA sent me these restrictions:

 

 

Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions. Every edition of SQL Server 2008 and later can restore a compressed backup.

 Restrictions

The following restrictions apply to compressed backups:

  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.  

The third refers to NTbackups but does it also applies to NetBackup? what about the other 2 and maybe some other I'm not aware of?

Any information will be more than welcome

 

Thanks

David

1 ACCEPTED SOLUTION

Accepted Solutions

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi,

 

The wording of point 1 would indicate to me that they're talking of Windows Backup, we dont have "media sets".

Point 2 seems perfectly valid, if a feature is released in version 2008, its expected that it would not be understood by version 2005. Take a look at LTO, a version 5 cartridge's compression would not be able to be understood by a version 4 tape drive. Its from the future, and foreign.

 

I've not seen any restrictions regarding this, in fact, there is not even a new guide released for SQL in version 7.0.1.

 

But who knows :p

 

R

View solution in original post

6 REPLIES 6

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi,

 

The wording of point 1 would indicate to me that they're talking of Windows Backup, we dont have "media sets".

Point 2 seems perfectly valid, if a feature is released in version 2008, its expected that it would not be understood by version 2005. Take a look at LTO, a version 5 cartridge's compression would not be able to be understood by a version 4 tape drive. Its from the future, and foreign.

 

I've not seen any restrictions regarding this, in fact, there is not even a new guide released for SQL in version 7.0.1.

 

But who knows :p

 

R

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified

I have also searched the 7.0.1 documentation and the only reference to this new feature is in Release Notes. No warnings, no how-to, no restrictions...

extract:

The NetBackup Database Extension graphical user interface for SQL contains a new option that enables you to use SQL server's native compression feature on a backup. That is a new feature for SQL 2008 and beyond. You can also add compression to your backup by adding the entry, SQLCOMPRESSION TRUE, in the SQL batch file.

There's a screenshot of the new GUI as well.

 

If you are really concerned about point 1 and 3, you might want to create a separate pool for the SQL compressed backups.

 

To be 100% sure that your backups are fine, please arrange for a test SQL server where restores can be tested.

davidbed
Level 3

Riian:  Totally makes sense about the media sets.  As for point 2, I reread the note from the DBA and you can restore this compressed R2 backup to a native 2008 SQL.  Still not much infos about this new feature

 

Marianne: Restore have already been tested, otherwise I wouldn't have asked about the restrictions.  Good point about the separate pool, but I will try first to see if its possible to have both backups on the same tape.

 

I will send an email to Symantec see if there's anything from their side.

Thanks to you guys, its appreciated.

David

davidbed
Level 3

the answer from Symantec is that there's is no other restrictions than what is stated in the release note and all the other restrictions I mentionned in my first message are related to windows Backups.

 

Guess I will do some more testing just to be sure but so far so good and it does compress alot.

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

NetBackup 1 - Windows 0 smiley

davidbed
Level 3

Just so you know:

1. compressed the DB from 4Gb to 680Mo

2. halved my backup time on a 4Gb database

3. halved my restore time on the same DB (it restores the compressed data)

4. uses any tape available wether its compressed images or not

5. quite simple, just add the SQLCOMPRESSION TRUE line in the script

So the restrictions were only for Windows backup

The average speed of backup is much slower when compressed though so it might be an issue if you have fast drives but overall its way faster to compress the databases even if the speed is slower.

 

David