May 27, 2009 at 11:27 am
Hi,
We have a production SQL Server 2005. Now Iam creating the Backup jobs.
My Requirement is as below:
1.Full Backup Daily 1:00 AM
2.Diffrential daily 6hrs
3.Log backup every 1 hour.
SO I did create 3 jobs
1.FullBackup - daily at 1:00 AM
2.DiffBackup - every 6hrs starting from 7:00 AM, 1:00 PM, 7:00PM and 1:00 AM(should I schedule this job to end at 7:00 PM, because at 1:00 AM full backup will run right?)
3.LogBackup -daily every 1 hr starting from 2:00 AM, 3.00 AM, 4:00 AM, 5:00 AM, 6:00AM, 7:00 AM, 8:00 AM, 9:00 AM, 10:00 AM, 11:00 AM, 12:00 PM, 1:00 PM, 2:00 PM, 3:00 PM, 4:00 PM,5:00 PM, 6:00 PM, 7:00 PM, 8:00 PM, 9:00 PM, 10:00 PM, 11:00 PM, 12:00 AM, 1:00 AM.
In above Differential and log backup schedule, there will be both Diff and log backups at every 6hrs
7:00 AM, both diff and log backups will run at same time
1:00 PM, both diff and log backups will run at same time
7:00 PM, both diff and log backups will run at same time
1:00 AM, both diff and log backups will run at same time
So, incase if we need to restore a databse until 12:00 PM,We will restore
1.Full Backup taken at 1:00 AM
2.Diff backup taken at 7:00 AM (at 7:00 AM, we have also Log backup. what should we do with that?)
3.Log Backup taken at 8:00 AM,9:00 AM, 10:00 AM, 11:00 AM, 12:00 PM
My questions are:
1. our Backup strategy is correct or not?
2.What will be the impact of having both backups diff and log at every 6hrs, incase of disaster recovery?
3. Creating 3 diffrent jobs for full, diff and log is correct or not?(I mean is this OK for production SQL Server?)
4. How and how many backup jobs you guys create?
5.If the full backup of one database takes more than 1hr from 1:00 AM to 2:30 AM, what happen to the Log Backup of of 2:00 AM? its going to be run? if its runs what should we do when restoring that database?
Thank you very much
Ram
May 27, 2009 at 12:02 pm
You might have problems trying to run Diff and Log at the same time. Offset one or the other by something like 15 minutes, and you'll be fine.
Three different jobs is fine for that.
As for it being "correct" or not, that will depend on your needs.
What I'd do is try recovering to a point in time, using the Diff backups, then try just using the Log backups. See which one is faster/easier. I'd had some databases where Diff backups made a big difference in how fast I could get back online, and others where they actually slowed things down a bit. Depends on what's going on in the database, so definitely test it.
- 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 27, 2009 at 3:00 pm
There might be a problems if you trying to run Diff and Log at the same time. Make difference of 15 Min diff.
Three different jobs is fine for that.
Correct or Not correct based on your requirements
May 27, 2009 at 4:40 pm
I agree with Pradyothana. Could not disagree more strongly with G2.
ROFL.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 27, 2009 at 4:44 pm
That's some time delay, if it's a cross post, I blame ZoneEdit
May 27, 2009 at 5:21 pm
You might have problems trying to run Diff and Log at the same time. Offset one or the other by something like 15 minutes, and you'll be fine.
Could you please tell me what kind of Problems we may get???
and please tell me what the Backup schedule of you guys follow(for full, diff and log). So that I can compare and take good decision on this?
and how you guys delete the old backup files???
thank you very much
May 27, 2009 at 10:04 pm
Actually, since neither FULL BACKUPS or DIFFERENTIAL BACKUPS truncate the transaction log, and starting with SQL Server 2005 TRANSACTION LOG BACKUPS can run while FULL BACKUPS are running, I see no reason why a TRANSACTION LOG BACKUP couldn't also run concurrently with a DIFFERENTIAL BACKUP.
(Too many capitalized words??)
May 28, 2009 at 7:36 am
Yes, you can run them at the same time in 2005. I've had some IO bottlenecks from concurrency in that case, but it's potentially not a big deal.
On the other hand, why overlap them like that? If you have a Diff and a Log at 7 AM, and decide to restore to 7:05 AM, you would probably end up ignoring the Log backup anyway, and use the 7 AM Diff and the 8 AM Log, instead of using the 1, 2, 3, 4, 5, 6, 7 and 8 AM Logs. That's probably more relevant than any technical issues with it, anyway.
- 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 28, 2009 at 10:08 am
It probably makes sense to stagger the start times of each type of backup. At least, between the differentials and the transaction logs. Starting the transactions logs on the half-hour and the differentials on the hour works just to keep them separate and easier to group.
So, with that in mind I have to agree with Gus (GSquared).
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2009 at 10:12 am
please tell me what the Backup schedule of you guys follow(for full, diff and log). Just give me an example, I want see how you guys avoiding the occurence of both diff n log backups at the same time.and how you guys delete the old backup files???
Thanks
May 28, 2009 at 10:34 am
GSquared (5/28/2009)
Yes, you can run them at the same time in 2005. I've had some IO bottlenecks from concurrency in that case, but it's potentially not a big deal.On the other hand, why overlap them like that? If you have a Diff and a Log at 7 AM, and decide to restore to 7:05 AM, you would probably end up ignoring the Log backup anyway, and use the 7 AM Diff and the 8 AM Log, instead of using the 1, 2, 3, 4, 5, 6, 7 and 8 AM Logs. That's probably more relevant than any technical issues with it, anyway.
It also depends on how long the differential backup takes to run. If there is a great deal of activity in the database it is possible that it may still overlap with a scheduled t-log backup. I was trying to point out that full and differential backups don't block the t-log backups anymore starting with SQL Server 2005.
June 1, 2009 at 6:43 am
Lynn Pettis (5/28/2009)
I was trying to point out that full and differential backups don't block the t-log backups anymore starting with SQL Server 2005.
Which is something I had (quite obviously) forgotten.
- 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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply