Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup Strategies Expand / Collapse
Author
Message
Posted Wednesday, May 6, 2009 9:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 16, 2012 3:51 PM
Points: 83, Visits: 300
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..
Post #711247
Posted Wednesday, May 6, 2009 10:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #711267
Posted Wednesday, May 6, 2009 10:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 40,630, Visits: 37,093
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #711273
Posted Wednesday, May 6, 2009 10:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 30, 2014 1:49 AM
Points: 92, Visits: 222
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
Post #711278
Posted Wednesday, May 6, 2009 10:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 16, 2012 3:51 PM
Points: 83, Visits: 300
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
Post #711289
Posted Wednesday, May 6, 2009 10:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
You can use a maintenance plan with only a maintenance cleanup task to delete backups.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #711292
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse