May 6, 2009 at 9:56 am
I am kind of new to this.
I am trying to implement the following backup strategies for all of our databases
Monday night - full backup
Tuesday through Sunday night - differential backup
Log backups every two hours.
My question is
1) Do I overwrite the same files everytime? I mean for the full backup (every monday night do I replace the exsisting one with the new backup file or do i create the new one?)
2) How about differential backup file. For e.g I will have one file for tuesday and another file for wednesday..Since I have file for wednesday which will have data of tuesday as well. Can I overwrite tuesday's file with wednesday's file and so on?
3) How about log files? These are probably I neeed to keep all the files since they run every two hours.
4) Is it good idea to create all the backups in the same folder or have three different folders for Full, differential and logbackup?
Thanks much..
May 6, 2009 at 10:21 am
The frequency and all that will depend on your backup and recovery needs.
So long as your full backups are going onto tape or something of that sort, overwriting the old one with a new one shouldn't be a problem.
The only reason to keep old diff backups is if you want to do a point-in-time to something before your most recent diff backup. In your case if it's Friday, and you want to do a point-in-time to Thursday afternoon, it'll be easier to restore the last full, restore the Thursday morning diff, then use Thursday log backups to bring it to the desired point, than to restore the last full and then restore almost a full week of log backups. If that's not going to come up, then there's no real point in keeping the old diffs once the new one is written.
Log backups, definitely don't overwrite those. That would defeat the whole purpose of doing them in the first place. Just make sure you dispose of ones that are aged out.
- 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
May 6, 2009 at 10:23 am
barunpathak (5/6/2009)
1) Do I overwrite the same files everytime?
You can, but what if you need to restore an older backup, either because the newer one is corrupt (it happens) or because someone wants to see the data as it was 2 weeks back?
2) Can I overwrite tuesday's file with wednesday's file and so on?
Again, you can because diffs contain data since the last full, but again, what happens if you go to restore and the Wed backup is damaged, or someone deleted data on tuesday and you need to restore to get it back?
3) How about log files? These are probably I neeed to keep all the files since they run every two hours.
For logs, you need an unbroken chain of log backups to restore from. So if you need to restore to say Thursday 10am, you need the last full backup, the wed differential backup and all log backups since the wed differential. It's a good idea to keep at least back to the last fill, if you have space, to give yourself several options for recovery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 6, 2009 at 10:28 am
You need any backup to recover database in case of failure. So, if you believe nothing going to happen after you have full backup – feel free to overwrite it. However, as a DBA with a long history I would recommend to keep:
-Four full tested backups on tapes off-site;
-All differential backups since last or last plus one full backup (otherwise there is no way to do recovery to certain time from last full). All of them on hard drive for fast recovery and at least some off site;
-Last two days of transactional backups.
With that schedule you can recover any problem that happen up to two days ago, some problems that happen from last full backup, data in condition of up to a month ago. Also, you can create rotation of backup media according to schedule.
Two hours or five minutes depends on the business requirements: if your business cannot afford almost any losses (banking) you need not only every minute logs but also mirroring off site, if your business can recover from 24 hour data loss – you do not need logs on every two hours.
Same or separate folders: it is up to you. I prefer three different so it would be easy to find what I need and clean up old files.
What is the reason to do full backup on Monday night? Does it mean that database has a lot of critical updates on a weekend? If not, do full on Saturday or Sunday night.
Also, I would recommend reading books for the MCITP: Database administrator self-preparation. A lot of useful information and have answers to your questions.
Alex Prusakov
May 6, 2009 at 10:40 am
Great!! Thanks for your replies guys...Now I have better understanding of what I need to do.
Lets say I want to keep 2 weeks of full backup only. Do you have script to delete the the old backups? We would want to use native sql on this not sqlsafe or other products.
Thanks again
May 6, 2009 at 10:44 am
You can use a maintenance plan with only a maintenance cleanup task to delete backups.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply