The more I read about backups the more confused
-
The more I read about backups the more confused about all the options I become. The basics are familiar to me, but I always start wondering if I have the right plan.
We run SQL 2k and do a full backup to disk each night. During the workday, we backup transaction logs every two hours. I keep five days worth of backups and transaction logs on the server (RAID 5). After the evening backup, everything is backed up to tape. The tapes get stored in a fireproof safe on the premises. At the end of the month, I run an extra tape and take it off-site
I always figure that worst case is a plane falls on the building and I lose all my current stuff and the current months worth of data. At that point, we more problems than the data, but I recognize that I should have more stuff off-site and maybe if it’s not off-site it should be on the tapes I have. If there is an equipment problem, I can restore everything but the last two hours, which in our case that is not a big problem.
I always wonder if I have too much data saved on the server. I can see restoring the most recent backup and the logs, but could I really need to go back and restore something from a few days back? If so, wouldn’t I restore the database to a different server and just grab the data. Otherwise how would anyone know at what point something was deleted or whatever.
Am I on the right track or am I missing something?
Thanks,
Bob
-
Sounds to me like you're pretty much on track. Since your backups for the past month are stored on premises, you have very quick access to your backups which are older than 1 day. In that case, you SHOULD be safe in deleting all but the most recent backup from the server. However, if you have space on the server, I'd leave them there. What if the tape is bad??? In your current situation, each backup file gets copied to multiple tapes. So, theoretically, you should have access to a particular backup file even if a tape is bad. Plus, if there is sufficient space on the server, and you need to restore the backup from 3 days ago to a new database to investigate a problem, you can do that on the production server, being careful, of course, to rename the database and the physical files. In that case, you can have the copy up and running pretty quick, as you don't need to find/load/restore the tape before starting your database restore.
You might think about increasing the frequency of taking backups offsite, though.
Steve
-
I would second the motion to increase the frequency of off-site backups.
I tend to look at a backup on a disk as insurance against cock-ups.
Backup on a tape is insurance against machine failure.
If you have the disk-space for 5 days worth of backups then fine, don't worry about it, but when things get tight have a prominent note to remind you or your successor that disk space could be scavenged.
We have had a server fail with a motherboard fault. This took a considerable time for the engineers to diagnose i.e. 2 weeks by which time they had replaced every single component in the server. The moral is don't run mission critical apps on obsolesent hardware, you won't be able to get parts for it in a hurry.
-
Thanks!
My primary concern is to be able to get back to as close to where we were should something happen. I will have to get a better grip on keeping backups off-site for me to realize that.
I sometimes wonder if I might need to get to where we were a day or two back. I can't imagine that we would ever need to try to get everything to where it was last Monday at 2:00 pm and then how would you fill everything in from that point on.
Given that we would never try to do that, there seems to be no reason to ever keep transaction logs after a full backup takes place. Is there?
Thanks again,
Bob
-
Yes!
I have had numerous situations where a developer/user/... wanted to see data as it existed before some change took place, or how the data existed at some certain point in time because a record was accidentally deleted and not caught til later, etc. If you have those transaction logs, you can get it back to exactly where they want it, right down to the second.
Secondly, I have had the situation occur, although rare, that a full backup was either damaged, missing, or otherwise unusable. In this case, you better have the previous full backup, and all of the transaction log backups since.
But, as David mentioned, you have flexibility with your current 5 days worth of backups on disk. If disk space is at all tight, you can reduce that without any worries.
I sent an e-mail, this morning, to one of my users advising that he start thinking about adding disk space because I just reduced disk retention of backups to 2 days, and changed the job to delete the old backup before taking the new. In this situation, we're still covered, but unless additional disk space is added, the next step is unacceptable.
Steve
-
If you want to be super secure you should consider having an off site disaster recovery box. Consider a strategy like log shipping in order to keep an off site machine in sync. This ensures that the "building disaster" does not result in much data loss at all.
-
The only concern I can think of is, please ensure the backup really works, I mean the data that is backed up is relly loadabale. This might be a stupid suggeston but it frm experience I say this
paul -
Not a stupid suggestion at all. I try to do test restores on a regular basis, both to maintain my skills and to ensure the validity of our process and media.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply