November 4, 2009 at 7:43 am
PiMané (10/31/2009)
damn.. forgot bout that :SThanks...
So, if there's no need to point in time restore, the best for us is to have single mode, with full backups and diff backups to the "critical" table's filegroup (will be small since there won't be that many transctions). Is it viable to have 5min dif backups with compression? We'll be doing the backups to a FC disks with write cache, since it's in a SAN.
Thanks,
Pedro
Keep in mind that each diff backup will take longer to complete and take more disk space than the one before it, till you run another full backup.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2009 at 9:36 am
You seem to be under the impression that you can restore the full database and then restore a differential backup of the one filegroup for your critical data. That is not a valid way to recover the database; you would still need to apply transaction logs to bring the database to a consistent state. It doesn’t matter if that is not important to you; it is just the way that SQL Server works.
Your best strategy would probably be to start with full daily backup and then transaction log backups scheduled for frequent intervals, like every 5 to 15 minutes. If applying all the transaction logs would make your recovery time too long, then you could add in differential backups at intervals during the day. Then for recovery, you would restore from the full backup, the latest differential backup, and then the transaction logs to recover the database.
November 4, 2009 at 10:18 am
I also made some tests are the differential backups ain't that small.. they are 4GB (compressed) at the end of the day, for the whole database witch takes a long time to do.
So probably we'll stick to FULL mode with daily full backup, 1 differential backup at midday and 5 or 15min intervals transaction log backups.
Does anyone know just how much slower are compressed backups to uncompressed backups?
At this time our database is 170GB, a full backup compressed is 104GB but it takes a long time to restore, almost 15min on a 2 QuadCore with HT machine (Xeon E5520) with 48GB DDR3 RAM... with a SAN storage with FC disks...
Is this due to the backups being compressed... yeah, i know, just do an uncompressed backup and try it yourself.. but i just want to know if anyone as had this kind of problems/issues...
Thanks,
Pedro
November 4, 2009 at 10:27 am
We are currently running SQL Server 2005 EE on our production servers. We use HyperBac to compress our backups on the fly and our backups and restores are faste than native backup/restore operations.
November 4, 2009 at 10:39 am
Thanks for the tip on HyperBac. I'll check it out.
Does anyone use Diskeeper for file defrag?
Is it the "best" out there or does anyone use something else?
Thanks,
Pedro
November 4, 2009 at 12:26 pm
Every time I've tested out various compression schemes for backups, they've sped the process up, not slowed it down. Makes sense - CPU and RAM are faster than disk I/O, and compressed backups use more CPU and RAM and less disk I/O than uncompressed ones.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply