Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steamngn
Constraint Violating Yak Guru
306 Posts |
 Posted - 2005-11-20 : 21:05:18
|
| Hi Again gang,How far back should I be keeping trans log and data backups? Presently I am backing up the logs hourly during the day, and the data each evening, then copying it to tape overnight. The tapes are overwritten weekly, and I am keeping backups on the server going back two weeks. Is this about right, or should it be more/less robust?AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-20 : 21:32:12
|
It really depends on your business. We never recycle our tapes, which means we have all backups for all time. That's rare though. A lot of people will have a cycle where they keep years permanently, a certain amount of months (maybe a year or two), a certain amount of weeks, and a certain amount of days. Get with the CIO and people in the business and figure out risk to cost and run with something. There's no "right answer" for this. I would prefer to have a minimum of two weeks daily though.  MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-20 : 21:48:18
|
| I usually backup the transaction logs every fifteen minutes, and do a full database backup each night. I keep the full backup on disk until the next full backup is created, and keep the transaction log backups on disk for 3 days. If you have enough space to keep more backups on disk, that is good. Don't forget to backup the system databases.By the way, we always backup to a disk array that is different from the disk array that the database files are on. That way, if one fails, we can still recover the database from files we have on disk.We backup the transaction log backup files and database backup files from disk to tape every day.I would retain the backups on tapes a lot longer than 2 weeks, depending on the application. Out standard is to keep all daily tape backups for 8 weeks, and to retain a monthly tape backup for several years.The most important thing is to consider all the possible failures that you want to be able to recover from, and make sure that your backup policies will allow you to do that. Think about what you would do after things like disk array failure, disk array controller failure, server motherboard burnout, data center destruction, accidental database drop, accidental deletion of data, accidental deletion of data that is not detected for several weeks. etc. Make sure you have a plan in place before you have a problem.Make sure you don't put yourself in a spot like this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57995CODO ERGO SUM |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-20 : 21:55:18
|
| Good points MVJ. We backup all our databases every 15 minutes also with full nightly. We keep two days of everything on disk though.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-21 : 01:21:16
|
| We work on decreasing granularity of backups as we go further back in time.We keep TLogs online for about 2 days. (They also get onto the tape backups, but not all tape backups are retained)We make a weekly Full backup and Daily DIFFs. We keep the DIFFs (online) for a week, and the FULLs for a month.We have daily tapes used in rotation (a week I think, maybe two). The tape from one day each week is kept for six months, and once a month the tape is kept forever.So we can go back more than six months, but only with a granularity of a month. Upto six months with a granularity of a week, and so on.We have audit tables on most of our tables; they store all previous changes to a record and allow the application to show previous-state of data - allowing diagnosis of accidental data damage, or that cause by program bugs etc. (by the users themselves). Purging of those tables is on a case-by-case basis, but mostly they work along the lines of "delete everything older than 6 months, but keep at least one record" - so if the most recent change to a given record is, say, 7 months ago the latest change will be kept, even though it was more than 6 months ago. This allows to work out how far back we have to go if ever we need to see older audit records.We keep "recent" backups on disk to avoid have to get a tape mounted and restored. The tapes are there for catastrophic failure, and fraud.Kristen |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-11-21 : 07:09:02
|
| Wow...I figured there would be a bunch of different answers, but I guess my beginner-ess didn't think it would be this far ranging. Good point, Derrick, that it will be different for every business. I have just taken all of this over (long story; Kristen knows), and I am green to a lot of this. This is not a very large company, and the past I.T. guy pretty much had free reign over what got done. This proved to be veeeeeeeeery bad, indeed. Many things need to change, and of course money is always an issue. What I see at this point is that I have two weeks worth of backup on disk, but due to the rotation of tapes only one week of it on tape. So, if the array craps out, I can only go back one week anyway. Am I right to assume that I should double the number of tapes so the tape backup set matches the DB backup set? for that matter, should the system DB be backed up daily (is this way now), or more/less frequently? Hang with me guys, I'm getting it as fast as I can!There's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-11-21 : 07:17:25
|
| MVJ:I just read that thread...Oh man, that has to hurt! We're not completely out of the park with backups, just perhaps not as robust as we should be. These are my first steps into taking all of this over, and DB maintenance is priority one. Once I have the backup issues sorted out, Is there any particular cleanup/maintenance stuff that is preferred? I have downloaded Tara's scripts to index/cleanup DB's, but I am not sure what the preferred frequency for running these are, or if there are other things that would be better/added...AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-21 : 09:03:31
|
I think you should get a lot more tapes than two weeks worth. You need to consider being able to recover data that was deleted several weeks ago that isn't discovered for awhile. From the answers on this thread, most people want to be able to go back at least sevral months or years.Better to have backups you don't need than need backups you don't have.quote:
Originally posted by steamngn...Am I right to assume that I should double the number of tapes so the tape backup set matches the DB backup set? for that matter, should the system DB be backed up daily (is this way now), or more/less frequently?...
CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-21 : 13:40:11
|
| Realistically you aren't going to recover from 2 weeks ago and repeat all the work in between ... or I don't think that's likely at least. You might want to recover some "old" data an compare it against current, or merge some stuff back in.1) What is the life cycle of your data? By that I mean if your data is valid for 20 years, and updated twice a year, you can expect that it may be sometime before users discover that something is "amiss". If your data has high change rates and turnover they will spot it sooner.2) If you data could be the target for any sort of fraud you have a need to be able to go back and unpick what happened. In that case you will need longer-period-backups and/or audit tables containing history of changes.3) If your users are General Public / Web Based then you maybe need audit data to help figure out what the users were trying to do - i.e. unlike office users you can't just trot round and peer over their shoulder, and ask them "So did you spot anything weird when the whole months payments disappeared?!"I don't see a problem with one weeks tape and two weeks disk - if the disk dies the chances are you will not have a need to go back to "stale data" so last night's tape backup is fine.If you drop a table and don't notice for 13.5 days your on-disk 14 day-old backup is just fine.However, there is a whole issue around whether 14 days backup gives you enough "elapsed time" to figure out what went wrong (Fraud, Long term bug that needs a list of "hat to fix", etc.), and if it isn't then you need to start "keeping" tapes - either all of them, one per week, one per month, or whatever granularity you need.Of course some of your tapes are off-site, right? And out-of-state too?For disaster planning we assume:1) Building burned down - a tape that is easy to get to, nearby, will do2) Area cordoned off - perhaps a gas leak, or plane landed on the area, or nuclear leak - whatever - now your nearby-tape is off-limits too.Apart from all of that Tape has the problem of "Will you be able to recover it in the future". Every few year we replace our tape drives with something "bigger & faster" and immediately render obsolete all our historical tapes ...Kristen |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-11-21 : 16:33:45
|
quote:
unlike office users you can't just trot round and peer over their shoulder, and ask them "So did you spot anything weird when the whole months payments disappeared?!"
I nearly shot coffee out my nose when I read this! This is our company in a nutshell... office users who love to "shoot from the hip" and ask questions after the fire is put out. After reading MVJ's and your stuff, I am getting a clearer picture. This is point of sales stuff, and we type a few hundred invoices a week. Not a ton, but each invoice gets A LOT of text data with it for each sale/service/delivery issue. The notes are very important, and we keep a customer history forever. Issues seem to be caught within hours usually, couple days at most. I think if we go to one months' worth of tapes, and leave the backups as they are, we'll be in pretty good shape.AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-22 : 01:54:42
|
| "point of sales stuff"I think you need to consider the possible fraud angle then ... and the ability to go a long way back to figure out what happened.Kristen |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-11-22 : 10:44:29
|
| I've been talking to the boss about this, and we're talking to the legal people about how far back they think we should maintain in backups. This is a very good point, the fraud thing, and I am not sure if what we are keeping is back enough or not. We've talked about a 'hard copy' of the DB and log onto DVD once a week and archiving it; this could be done indefinitely, and the cost would be minimal. Just a matter of setting it up and doing it once a week...AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-22 : 11:34:39
|
| There are plenty of other good reasons to keep archive copies of the database. For example, next year, you discover that someone has accidentally deleted the year end reporting stored procedures. The point is that it isn't easy to anticipate all the possible reasons why you may need it.If you don't have a backup when you need it, you can be fairly certain that it will be seen as your fault. "You should have told us this could happen!" I always tell people that DBAs never get fired for having too many backups.I am not sure what you mean by a "hard copy" of the database.. I hope you are talking about copying database backup files onto the DVD, and not the database files themselves.CODO ERGO SUM |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-11-23 : 19:50:12
|
quote:
If you don't have a backup when you need it, you can be fairly certain that it will be seen as your fault. "You should have told us this could happen!" I always tell people that DBAs never get fired for having too many backups.
I hear you! Since I am brand-new to this (I just got this position on a fluke (thanks Brett!)), I am trying hard to cover all of the bases. There can never be too much of good thing in this case, as far as I am concerned.quote:
I am not sure what you mean by a "hard copy" of the database.. I hope you are talking about copying database backup files onto the DVD, and not the database files themselves.
Yup, I mean copying the backups to DVD. I am thinking of putting another hard drive in the box, and putting the backups there. Then copy the backups to DVD on a weekly basis or whatever timeframe makes it convenient to fit the backups on disk(s). There are a total of 3 databases to backup on this particular system plus the master. I would have to put each on a separate DVD due to size (there is one that would probably fit with the master), and i would set the copy schedule to whatever timeframe seems to be close to the capacity of the DVD for each DB. I was talking to our hardware vendor about this, and he suggested looking into a NAS as another alternative. The DVD's can be kept offsite, and the TCO is pretty cheap this way. The only drawback I see to this is that I can't automate it!AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-23 : 20:52:07
|
| You might consider using PKZIP to compress the database backup files. You can usually get about 80 to 90% compression, so you might be able to reduce the amount of media you need. It is an extra step, but you can automate it with a SQL Server Job, and the PKZIP command line version (non-GUI). I believe you need PKZIP 5.0 or higher to have a version that supports compressing files larger than 2 GB.A more powerful alternative is to use LiteSpeed backup. It provides good compression and integrates directly with SQL Server. It has other benefits too, like reducing backup time, and reducing the amount of disk needed to hold backups. I don't use it, but I have heard many people here praise it. The disadvantage is that you have to buy it. You may want to read this about it:http://www.sql-server-performance.com/sql_litespeed_spotlight.aspWhy are you planning to use DVD's instead of tape, especially since it sounds like you already have a tape backup system? Are they cheaper than tapes?CODO ERGO SUM |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-11-23 : 21:20:48
|
quote:
Why are you planning to use DVD's instead of tape, especially since it sounds like you already have a tape backup system? Are they cheaper than tapes?
Weeeeell...The answer is 'sort of'. we do have tape backup on this system. Keeping a large tape library is a possibility, and the cost is just slightly higher than the DVD scenario. There are a few things in favor of the DVD setup:1) DVD's take up much less space than tapes,2) They are more durable than tape, and3) It is quicker to retrieve from the DVDs than from tape.The disadvantage is the manual backup process. I have not checked out the litespeed stuff, but I sure will. I think the big hangup around here with a tape library is that the company has been through the 'so-sorry-the-tape-can't-be-read' thing once or twice, and everyone is kind of leary about relying on them. But perhaps it is the best way in the end. What we really need is a 100GB DVD! (Would you believe it is 9:19 on the night before T-day and some buffoon is bugging me about RTF text? Go HOME already!)There's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
|
|
|
|
|
Backup history: how long?