February 8, 2012 at 12:18 pm
Howdie Gents,
I would like to ask everybody's opinion on our company's sql server 2005 backup maintenance plan. I'm by no means a DBA and have inherited a database that serves as a back-end of our e-commerce website that runs 24x7. Below are our backup schedules. Please feel free to critique, bash, suggest, laugh and I promise it won't hurt my feelings 🙂 I have some doubts about the Transaction Log schedules but would hold my questions after I see what others have to say.
1. FULL- Occurs once every day at 5:00am
2. DIFFERENTIAL- Occurs every day every 6 hour(s) between 11:00:00 AM and 11:59:59 PM
- 1st backup = 11:00am
- 2nd backup = 5:00pm
- 3rd backup = 11:00pm
3. TRANSACTION LOG
Schedule #1 - Occurs every day every 5 minute(s) between 12:00:00 AM and 4:59:59 AM
Schedule #2 (After Full) - Occurs every day every 5 minute(s) between 5:10:00 AM and 10:59:59 PM
Schedule #3 (After DIFF1) - Occurs every day every 5 minute(s) between 11:10:00 AM and 4:59:59 PM
Schedule #4 (After DIFF2) - Occurs every day every 5 minute(s) between 5:10:00 PM and 10:59:59 PM
Schedule #5 (After DIFF3) - Occurs every day every 5 minute(s) between 11:10:00 PM and 11:59:59 PM
Thanks everyone
February 8, 2012 at 12:25 pm
Looks fine.. Looking at the schedule, I am guessing the database is not a very large database..
All in all I don't see any problems with that schedule..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 8, 2012 at 12:28 pm
Why 5 schedules for the log backups? Just one schedule (every 5 minutes, all day) would be easier and less confusing if you need to restore.
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
February 8, 2012 at 12:34 pm
GilaMonster (2/8/2012)
Why 5 schedules for the log backups? Just one schedule (every 5 minutes, all day) would be easier and less confusing if you need to restore.
What Gail said. One schedule to rule them all.
Keep in mind RPO/RTO (Recovery Point Objective and Recovery Time Objective) if there are any specified in an SLA hidden somewhere. Your current schedule has a potential data loss of 10 minutes. Gail's suggestion would make that 5 minutes.
Don't forget to keep an eye on those logs too, make sure you have enough disk space.
February 8, 2012 at 12:41 pm
At first, I thought the same as Gail mentioned but after looking at the schedule for the 2nd time, I am assuming whoever has created the Maintenance plan and schedule might have created it such a way that both Full and Log backups do not run at the same time at 5 AM. And also Diff and Log backups do not run at 11 AM, 5 PM and 11 AM..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 8, 2012 at 12:53 pm
Full and Transactional Log backups are not mutually exclusive. A Log backup can occur when full backup is running.
February 8, 2012 at 12:58 pm
Bru Medishetty (2/8/2012)
At first, I thought the same as Gail mentioned but after looking at the schedule for the 2nd time, I am assuming whoever has created the Maintenance plan and schedule might have created it such a way that both Full and Log backups do not run at the same time at 5 AM. And also Diff and Log backups do not run at 11 AM, 5 PM and 11 AM..
Yep, I think that's what the person who created the schedule had in mind. He doesn't want the transaction logs to be running while the Full or Differential backup plans are executing. So... my question is.. should this be a concern? Do you guys agree to GilaMonster's suggestion of one schedule to rule them all?
Appreciate the fast feedback guys!
February 8, 2012 at 1:06 pm
SQLback (2/8/2012)
Full and Transactional Log backups are not mutually exclusive. A Log backup can occur when full backup is running.
I have not said that they are mutually exclusive...
It is for better clarity not to have a 2 different kinds of backups running against the same database, and coincidentally I saw a thread here at SSC today on that topic.. (I don't have the link to it..)
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 8, 2012 at 1:19 pm
Bru Medishetty (2/8/2012)
I am assuming whoever has created the Maintenance plan and schedule might have created it such a way that both Full and Log backups do not run at the same time at 5 AM. And also Diff and Log backups do not run at 11 AM, 5 PM and 11 AM..
I understand the intention, the reason behind that is the question. There is no good reason to not run log backups during a full backup. It increases the data loss risk (10 minutes instead of 5) and complicates the restore sequence (is this 10 min gap intentional or is there a log backup missing)
It is for better clarity not to have a 2 different kinds of backups running against the same database,
If you were talking about full and diff, I'd agree, but not full and log or diff and log. No reason at all to stop the log backups during a full or diff backup. They can run concurrently and do not affect each other. You gain little by stopping the log backups and lose some recoverability options.
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
February 8, 2012 at 2:02 pm
I'm almost convinced that I will be fine with one schedule for the Transaction logs. But I do have a couple of questions before I modify my maintenance plan.
The .bak and .trn files "date modified" attribute will be the date the backup finishes right? If not, never mind the next question.
If a transaction log backup runs and finishes in the middle of a long running full backup, it will have a modified date that is earlier than the full backup.
If this is the case, when there's a need to restore, this particular transaction log would not be picked up by the user since it's older than the full backup. Does this makes sense?
February 8, 2012 at 2:08 pm
crawler486 (2/8/2012)
I'm almost convinced that I will be fine with one schedule for the Transaction logs. But I do have a couple of questions before I modify my maintenance plan.The .bak and .trn files "date modified" attribute will be the date the backup finishes right? If not, never mind the next question.
If a transaction log backup runs and finishes in the middle of a long running full backup, it will have a modified date that is earlier than the full backup.
If this is the case, when there's a need to restore, this particular transaction log would not be picked up by the user since it's older than the full backup. Does this makes sense?
Yup, perfectly correct.
Btw, even though it will work in most cases, your restore scripts shouldn't be looking at completion time to decide which backups to use. If a full and a log backup complete at exactly the same ms, the log backup is almost certainly necessary. Your restore scripts should be looking at the LSNs either from RESTORE HEADERONLY or from the backup tables in msdb.
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
February 8, 2012 at 3:26 pm
GilaMonster (2/8/2012)
Yup, perfectly correct.Btw, even though it will work in most cases, your restore scripts shouldn't be looking at completion time to decide which backups to use. If a full and a log backup complete at exactly the same ms, the log backup is almost certainly necessary. Your restore scripts should be looking at the LSNs either from RESTORE HEADERONLY or from the backup tables in msdb.
Thanks. But I lost you at LSNs and restore scripts as I have only tried SSMS to restore stuff but I think LSNs would be a separate topic 🙂
February 10, 2012 at 6:42 am
I just started working on an article on the backup header, so I can't point to it yet, but basically, the full backup has a marker for where it's at within the log, the Log SEquence Number. It marks the start & stop LSN. The Logs also have a start & stop LSN. You should be coordinating the LSN's, not just looking at date & time on the files. That's what the header info is for.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 10, 2012 at 8:06 am
Grant Fritchey (2/10/2012)
I just started working on an article on the backup header...
Oh good. Saves me from writing one.
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
February 10, 2012 at 8:51 am
Grant Fritchey (2/10/2012)
I just started working on an article on the backup header, ...
Yay! I'm almost completely out to lunch on the subject of backups. I've been doing a full backup by hand every few days and keeping my fingers crossed that I don't run into any major catastrophes until I get up to speed on the matter. Fortunately, it's not a high-volume database and it's on a RAID-5 array, but I'm still not happy - I know I should be doing a better job of protecting things, but I'm pretty new at SQL Server and I'm the only guy here who knows anything at all about it, so I'm swamped.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply