| Author |
Topic |
|
kish
Starting Member
45 Posts |
 Posted - 2004-03-26 : 02:15:39
|
| Are sql server backups compressed..if yes then what is the compression rate....if no then can we expicitly compress it ? |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-26 : 08:56:54
|
| SQL Server does not compress the backup files. If you have a 60gb database, you need to save 55-59gb in space for the backup file. You can then compress the backup file using any standard comression utility and save quite a bit of space.You might also want to look at SQLLiteSpeed.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-03-26 : 13:31:41
|
| I've used WinRAR to RAR up my database backups, and you can make them MUCH smaller once you RAR them. It takes a good bit of time, but dependind on what you are trying to do it may be worth it.Be mindful of Derrick's comment DB Size ~= Backup file sizeMichael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-26 : 13:36:35
|
| We use SQL Litespeed for our larger databases. It compresses the file as it is backing it up. This not only helps with reducing the backup file size, but it also backs up the database much quicker than the native backup command.Tara |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-03-26 : 14:22:10
|
| You can also set NTFS compression on for the folder your backups go to. This will save space on disk, but the file will be expanded if you move it to some other location.There is also some overhead associated with NTFS compression, and there is also the possibility for massive file fragmentation if you ever get close to running out of disk space.-ec |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-03-26 : 14:31:22
|
| <Yoda>Skinned this cat in many ways we have.</Yoda>Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-03-26 : 15:13:51
|
| Just be sure if you are compressing your backups, that when you test them, you test them by starting with the compressed version, uncompress it, then restore it. Just testing the uncompressed restore provides you no assurance that the compression hasn't somehow corrupted your file. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-03-26 : 16:14:28
|
| A backup is not a good backup unless it's tested.I can't tell you how many times I've seen people run backup's of things and when tey need to restore they discover that the backup's had been failing or not getting all of the files because they were in-use etc.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2004-03-26 : 16:49:10
|
| Another vote for SQL Litespeed. Easy and painless, and as Tara says, both smaller and faster than the built-in backup stuff.And yes, *always* test backups from realistic scenarios. Doing frequent txlog backups so you can restore to a point in time? Test that. Doing full + diff + tx? Test restoring from full + diff + tx.Cheers-b |
|
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2004-03-30 : 12:56:43
|
| We use litespeed and have had several issues with it. The new version 3.124 is supposed to fix our issues, but I am still testing it. The main problem is failure to restore transaction logs. We do log backups every 15 minutes. I have had numerous failures restoring 1 of the transction log backups in the series. I get an invalid file pointer error even though no errors were generated during the backup and verify. I am able to work around it by extracting the backup to the native tape format and restoring it using the native sql commands. The product saves us a ton of time and space, but I recommend having a regular test restoration plan if you use it. I have also found the product to be very sensitive to the load on the server. Our production box is heavily loaded and I have seen a lot of failures/probelms backing up under these conditions than on our test boxes which have a lighter load.Lance Harra |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-30 : 13:03:40
|
| Thanks Lance. On the system where we use SQL Litespeed, we do not perform transaction log backups. We use SIMPLE recovery model and can afford to lose the data since the last full backup as we can recreate the data. If we decide to use the product on other systems where we can only afford to have less than 30 minutes of data loss, I'll keep your problems in mind.Tara |
|
|
|
|
Backup Compression