| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-11-21 : 07:11:08
|
| When I post in the database administration forum, I have so little experience with this topic that I imagine my posts may be unbelievable to experienced readers. This is one of those posts.Nightly (off-hours) backups are done on our production database, and I'm pondering what kind of backup might be done on an hourly basis to minimize data loss should the database be corrupted. A RAID disk subsystem is in use so I'm less concerned about recovering from disk failures than possible soft destruction of data.Terms like replication float through my mind, but I'm unfamiliar with the overall costs of getting trained and supporting replication. I suspect replication would require a larger investment than a possible backup snapshot to the RAID disk subsystem.A quick backup to the same disk media sounds reasonable and would cover soft data failures - (there is a 24 hour backup on tape elsewhere). I'm not confident that running a backup wouldn't adversly affect database performance during production hours. Keeping the performance hit to online users to a minimum is a concern.I've even considered writing a stored procedure that copies critical tables as opposed to backing up the entire database which is 1Gb now, probably growing to 5 Gb tops.Thoughts appreciated.Sam |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2003-11-21 : 08:01:56
|
| Software corruption is best handled with transaction log backups, lots of folks do them every 15 minutes. Recovery in EM is point and click. Hardware corruption from your description is best handled with log shipping which creates a fail over server, that is ready to go in short time with manual intervention.The hurdle is getting a concept of the various recovery models and how each effects database state. 2000 or greater you can let the wizard guide you and the results and recovery from transaction logs seem pretty obvious. The recovery model is defined by switches on the Backup Command and db options.Replication dosen't fit well in a disaster recovery model. It is to spread the data geographically, or to make data available when other solutions are unworkable.Tenacious O |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-11-21 : 08:27:07
|
| Is there a good dummies book on this subject? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-21 : 13:18:47
|
| 1-5GB is not big at all. It should back up in less than 5 minutes. We do full backups once per day, plus transaction log backups every 15 minutes. All files are copied to our disaster recovery site using log shipping. You do not need to use log shipping though. Do you need disaster recovery capabilities? Do you have a disaster recovery site? Or are we just talking about failures within one site?I agree with Sitka about replication. It is not for backup or disaster recovery scenarios. Replication is for copying data to another server for an application. I don't have any books to recommend as I have received my knowledge through on the job training starting with being a student work in the DBA group. But one book that I am fond of is Inside SQL Server. It is written by MS and goes into detail about how things work.Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-11-21 : 13:36:08
|
| Thanks Tara. I'll do some reading up on Transaction Log Backups.Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-21 : 14:06:17
|
| The important thing to know about transaction log backups is that it gives you the ability to perform point in time recovery. So let's say you have a full backup at 1am. You also have transaction log backups every 15 minutes. Now let's say that someone deletes millions of rows at 11:50pm from a table but wasn't supposed to. To recover to the point prior to the delete, you can restore your full backup, then start applying the logs until you get to the one taken just after the delete. Using that one, you tell it to stop at 11:49pm. Now you've got your data back. On harder failures, you might not have the transaction log after the failure occurs, so you can only restore to the last transaction log taken, which in the 15 minute scenario would be at about 11:45pm. How often you backup the transaction log depends on your environment.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-21 : 14:09:46
|
| Sammy,What are you doing now?The easiest is to just set up a maint. plan...Dump db 4:00am every day...delete files older than 2 weeksDump tranny every 15 minutes...delete files older than 2 weeks..(Why 2 weeks? because I've got the room)Optimize and Integrity check every sunday morning at 2:00 amDon't forget to dump the system databases once a week...Brett8-) |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-11-21 : 14:45:59
|
| NADA. Boopkas. No Transaction Logging. Just weekly full and nightly incrimental backups. Sounds like Transaction Logging is the thing to do. I'll look into setting it up as soon as I finish the inbox I'm working on now.Sam |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-21 : 16:44:14
|
| Sammy boy workin without a net....Brett8-) |
|
|
|
|
Question on frequent backups