February 12, 2010 at 6:42 am
I keep a week on disk for speedy restore (then tape to corporate policy rention), instances can have different backup plans but keep the diff and transaction logs in between. My reason being that you deemed it important enough to backup in the first place so you should the file for as long as corporate policy dictates.
The have been occasions when I have been asked to restore a previous days database to a point in time before the last full backup to retreive a data change that may have only been in place for small amount of time between backups. Without the tlogs.......
At my current position i have also been asked to assist in debugging a nasty legacy app by restoring databases to a particular days closing position to recreate errors. Without each days diffs.....
I suppose my point is that a comprehensive backup policy costs but you should always aim for it unless the business says not. Even then they should be made aware of possible issues no matter how improbable.
There are other ways of keeping your disk usage down. We use Litepseed (sorry redgate) extensively as the licenses for software is much much cheaper than the SAN storage. We are also invested in de-dupe technologies for all our backups to replace most of our tape libraries which is giving some encouraging results.........they just won't let me on it yet 🙁
Chris
February 12, 2010 at 6:46 am
We keep 1 or 2 (depending on DB) full backups and necessary tlogs on disk (which is mirrored to another location).
TSM system pulls to tape each night (also at a different location). Most tapes are kept 120 days with a copy sent offsite. Certain backups (year end HR) are also archived to tape for seven years. I have yet to need to restore a SQL Server backup from tape, but they are there if I need them (hope today's not my first).
Mike
Mike
“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey
___________________________________________________________________
February 12, 2010 at 7:12 am
We keep one week of full backups (one per day) along with t-Logs. We sometimes need to go back in time at a certain point to look at data.
February 12, 2010 at 7:29 am
I keep 3 days full backups and logs on disk. I've learned the hard way; hope for the best, but prepare for the worst. That way you won't be disappointed.
February 12, 2010 at 7:30 am
Of course it's different for any given circumstance. But regardless, I like to think that - say Monday is a holiday. When Tuesday morning comes, I would like to be able to restore a database to some point on Friday if necessary.
February 12, 2010 at 8:02 am
As my SQL Server backups are usually a mix of backup and archive:
Backup: the ability to restore a system to functionality following a problem (DML or DDL (DROP) mistake by someone who had approved access, malicious or accidental DML or DDL by someone who shouldn't have had approved access, actual attack (SQL Injection or other), heat death of the machine, hard drive tray simultaneous failures, RAID single failure + pre-existing corruption that had been masked by the RAID controller, HBA failure, power outage + RAID battery failure).
Archive: the ability to see what the system/data looked like at a previous point in time (app error investigations, bad data investigations, bad data recovery of specific subsets of data (Hey, we loaded one bad file N days ago, but the other 1,000 files and million transactions were good; fix the one bad one!) SEC retention regulations, legal investigations, etc.)
I usually keep 3-4 weeks of Fulls (weekly for Full recovery model, daily for Simple recovery model) and 2 weeks of T-logs (daily, most often), and alter the frequency as necessary.
I try to make very sure that the backups are _not_ written to the same physical disk spindles as the main DB files, as storage is the primary point of hardware failure leading to unrecoverable, or corrupt, files.
As far as corruption goes, I have absolutely seen corrupt files, but more often, I hear something like "Hey! Something weird happened! The file must be corrupt!" with absolutely zero evidence of corruption whatsoever.
I do have a plan that for some backups, I will have a separate app server generate .par2 Reed-Solomon ECC files once the backups are complete, which will allow the detection of corruption that may happen later, and the possibility of correcting it.
http://www.chuchusoft.com/par2_tbb/index.html - note that this software has a ~1GB limitation on the .par2 dataset, so a 900GB backup file can't have .par2 of more than 0.1% generated with par2_tbb. If anyone knows of better ECC software, please let me know.
As far as space goes, if your backup system doesn't do deduplication, and you're not already writing compressed backups, you could also set up compression with whatever tool your organization prefers (7-zip or other), either from the DB server (using up more DB server CPU and IO capacity) or from a separate app server.
February 12, 2010 at 8:21 am
Good discussion. Like many other I too keep at least 2 full backups and the trans logs between. I know there really is not a need to keep the logs between the 2 full backups but I find it easier to delete the items all at once.
I also "hear it" from our IT group about disk space. They use Veritas to also backup the DBs nightly but they don't run transaction log backups, they only keep 1 backup and guess what?? they don't verify the backups. 1 time many months ago, I trusted IT and wasn't doing my own backups. Then the VP of Development personal workspace db got corrupted (I'm not going into get into how it happened here, I'll save that for another day). It was a disaster, it took 6-8 hours for IT to "find" the db (after they backup using Veritas they move the physical files to a different SAN). Since they only keep 1 full backup they didn't realize the DB backup was corrupt. Ever since then, I've been told to keep our own backups no matter what.
February 12, 2010 at 8:38 am
No, 2 days of full backups are not enough. There was a hack that made rounds 7-8 years ago that got everyone over a holiday weekend. At a minimum, you need to make sure you have enough days backups to cover the longest period your company isn't working and you might not know you have been bitten.
Also, backups aren't only used when you have corruption or intrusion, there are times you need it to recover data for an end user. If you can afford to keep month-end backups, that is great and may be sufficient on top of the number of daily's needed to ensure you have a good backup in the case of corruption or intrusion.
February 12, 2010 at 8:40 am
Wow. Reading everyone's post, it sounds like many have a problem with 'IT' or 'System Admins' whining about DB's taking up to much disk space. Disk space is cheap! We keep a log on database growth and when we need more disk space, we order it.
Anyway, ignoring off-site tapes, I have 3 days of back-ups and logs. Right, don't need the logs, but we have a script to clean it all up. Mainly, we have 3 days to get through the weekend. It was 2 but when I came in on one Monday morning, no backups! Nothing was wrong with the DB, just network failure for the backup, so any easy leason for me.
Also, I restore from back-up about once a week for various test environments for developers and data analysts (I wear those hats too.) to muck about in. The db's are anywhere from 75 -200 GB.
February 12, 2010 at 8:41 am
I keep the last full backup and all the log files associated with it only. I manage over 100 servers/clusters that vary from a few gigs to a few terabytes of data. On some of the systems differentials are kepts as well.
There's another team that backups those backup files to tape. Part of my DR is to test their part and request a backup X days old and restore it to a test or dev server. I'll pull a random day from a random server at least once a week and log what I've tested. I definately believe in the philosophy that your backups are only as good as your last restore.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
February 12, 2010 at 8:56 am
We keep backups for Monday through Friday, cycling through. Logs are done approximately every 2 hours during business hours.
But while our databases are important, they are relatively small. Keeping 5 full backups plus logs is no big deal for our databases on today's computers.
February 12, 2010 at 9:18 am
I agree with GSquared. There's no magic number; it depends on how much time might pass before problems are detected and how hard it is to recreate the data when they are.
I once had to send a server's hard drive out to a well known disk recovery lab because HR hadn't been backing up their system as directed. Once management got the bill for that, it wasn't hard to justify the cost of better backup software and additional disk space. 😀
February 12, 2010 at 9:32 am
Alan Vogan (2/12/2010)
Wow. Reading everyone's post, it sounds like many have a problem with 'IT' or 'System Admins' whining about DB's taking up to much disk space. Disk space is cheap!
Enterprise disk space is actually not very cheap.
A 1TB consumer drive may be about $100, but 1TB of SAN space is probably over $3000 or so... just based on the price of a tray of 15k FC drives minus the hot spare minus RAID-5/6 overhead. Call it $5000 or more per TB for RAID10. Then add in more for the SAN box, additional licensing, fiber switch ports, power and cooling requirements and so on and so forth.
Then you run out of space on your current SAN, and need to fund the start-up costs of a new one... if your data center can support another one power and cooling and space wise.
February 12, 2010 at 9:34 am
We keep 2 weeks of full and logs. After that it has to be retrieved from tape. We have had frequent requirements to provide point in time proof of the data for some period within the prior two weeks.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 12, 2010 at 9:58 am
Nadrek (2/12/2010)
Alan Vogan (2/12/2010)
Wow. Reading everyone's post, it sounds like many have a problem with 'IT' or 'System Admins' whining about DB's taking up to much disk space. Disk space is cheap!Enterprise disk space is actually not very cheap.
A 1TB consumer drive may be about $100, but 1TB of SAN space is probably over $3000 or so... just based on the price of a tray of 15k FC drives minus the hot spare minus RAID-5/6 overhead. Call it $5000 or more per TB for RAID10. Then add in more for the SAN box, additional licensing, fiber switch ports, power and cooling requirements and so on and so forth.
Then you run out of space on your current SAN, and need to fund the start-up costs of a new one... if your data center can support another one power and cooling and space wise.
I still consider that cheap compared to the alternative of losing your production database. Of course, SAN's are the most expensive form of storage as you're paying for all of that management software, memory for caching etc.
There are places for saving money in IT but storage for backups ain't one of them.
"Beliefs" get in the way of learning.
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply