T-SQL Tuesday #158 – Stop Taking Transaction Log Backups

This week’s T-SQL Tuesday comes from Raul Gonzalez who asks about worst practices that aren’t always bad. Feel free to check out the full invitation by clicking the T-SQL Tuesday logo.

As I rattled my brain for instances of using “worst practices” in an arguably good way, the story below came to mind. Having database backups for sensitive data is always a best practice, but that doesn’t mean there isn’t a time when it’s better to turn that backup job off.

Struggling To Keep Up

Many years ago, a friend was using a backup/recovery tool for managing their backups. This particular tool on its own wasn’t necessarily bad. But in this case, it didn’t work well. In fact, it barely worked at all. Backups were slow to complete and restores were even slower. Attempting to restore even one database could take 10-15 minutes just to navigate a GUI and start the restore process.

It…was…very…slow…

Eventually, it got to the point where hourly transaction log backups weren’t able to keep up. Based on backup notifications, successful backups every hour turned into maybe every two or three hours. Sometimes even longer. That couldn’t go on and it was time for a change.

If You Want Something Done Right

Migrating all backups to a new backup solution wasn’t going to happen overnight due to various levels of red tape. Still, some type of temporary fix was needed to have reliable transaction log backups available just in case. They put together a transaction log backup process using Ola Hallengren scripts for transaction log backups, did some testing, and went forward with the process of managing transaction log backups one new way while the rest of the backups continued to be taken with their backup software.

Better Than Nothing

It’s certainly a worst practice to not take backups and not be able to recover data if there’s a failure. They were still technically taking backups (just a different process) and when the time came to cutover, there was nothing wrong with reaching out to the backup team and requesting that they “stop taking transaction log backups.”

Thanks for reading!