1

I have an elementary question. I just started as a DBA with no administration experience and upon reviewing the Backup schedule, I noticed on one instance that the maintenance plan that performs the backups is set up with 4 steps. The first and last steps are maintenance, but the second and third are to take Full backups (2nd step) and then take T-log backups (step 3) of all user DB's.

The job that calls the maintenance plan is run at 6AM and 4PM. I am confused why they would take the FULL backups first and then T-log backups directly after the FULL? It would make sense to me if it was the other way around. This is how it makes sense to me at least:

->at 610am, FULL backup completes

->at 4PM, take t-log backup to backup the t-log since 6AM

->at 410PM, take FULL backup

->at 6AM, take t-log backup for data since yesterdays 410PM FULL backup

->at 610AM, Take full backup

Can anyone think of a good reason for taking a FULL and then a t-log directly after? I tried asking the employees that have been here but they are clueless.

  • Without going into detail as to why this is set up, what is your RPO and RTO? Why aren't log backups being taken more often? You could lose data from 610AM until 4PM. These long gaps are more important than taking log backups before or after the full backup finishes. – Randi Vertongen Mar 20 at 21:45
  • 1
    I am the first official DBA. Just started and am to the point where i am comfortable with re-schedulnig backups. I realize that this a big gap, and I have talked to my superiors about it, but they said this has worked for them for years. I have a meeting with my manager and the CTO to explicitly create our RPO and RTO. But are there any unobvious implications by taking the Full and then the log directly afterwards? (BC on another instance, same schedule but it is LOG and then Full) – DBA_Kyle Mar 20 at 22:00
  • 1
    "worked for them for years" means that they didn't have a crash (loosely speaking) for years and they are betting on not having one in the future. Sorry for the sarcasm, not intended for you, Kyle, but for management. One can speculate why they did it like that did. Perhaps they wanted to "protect the timespan" and do a log after the ful, where using that log backup to be able to restore to any point in time, including for when the full backup was running? But, that log backup could have been taken at any point in time, of course. Probably no real reason for this, my guess. – Tibor Karaszi Mar 21 at 8:30
3

Can anyone think of a good reason for taking a FULL and then a t-log directly after? I tried asking the employees that have been here but they are clueless.

The reason I can think of is to 'truncate' the Transaction Log since the last full backup happened. If you would not do these log backups, the log would keep on growing.

If truncating the transaction log is the main reason then you should just set your database to the SIMPLE recovery model instead. This means that you will lose data up until the last full/diff backup.

But it would be no different with the current method of taking backups.

Unless some restore procedure is using STOPAT to restore to a point in time after the log backup has happened. (this still means data loss up untill the last backup, possibly +10h)

Another reason could be that the database is part of an AlwaysOn AG, but this should give you even more incentive for more frequent log backups.

The main issue

The main thing is, the log backups cost the same, wether you run them every minute,every 15 minutes or every 10 hours. Take a look at this blog post by Brent Ozar as to why it should not matter how often we schedule this (without getting too crazy).

Running them once before every full backup is not what they where designed to do, you should schedule them more often.

Something I prefer is this backup solution by Ola Hallengren, instead of maintenance plans.

Long story short, I would schedule log backups more often, the default people tend to use is every 15 minutes, but it depends on your RPO.

But are there any unobvious implications by taking the Full and then the log directly afterwards?

There should not be any issues with doing this, other than the remarks mentioned above.

  • 1
    Thank you Randi! I believe you are correct in that it is to truncate the log! – DBA_Kyle Mar 21 at 13:33
  • @DBA_Kyle Awesome! Good luck with the supervisors :) – Randi Vertongen Mar 21 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.