A post appeared on SQL Server Central two days ago wherein the person asked why the transaction log backups were suddenly taking a long time, having gone from under 12 minutes to over two hours.
Subsequent questioning revealed that they do one transaction log backup daily.
Most experienced DBAs would be kind of aghast at the concept of doing transaction log backups once a day. If the database is the least bit transactionally active, this would represent a potentially devastating data loss if the database fell in to the bit bucket.
Obviously any database will have different recovery objectives for time to recover and acceptable data loss depending on business needs and the size of the database. Jeff Moden talks about his system where old data, which must be kept online, is kept in its own filegroups so that only active data is backed up, greatly reducing size and time required for data backups and making for much faster database recovery in the event of an emergency restore (restore the active data, they’re back in business and they can take time recovering the archived data). The only scenario that I can (off-hand) envision where a once daily t-log backup would be acceptable is if the database does an ETL process and is then quiet transactionally thereafter. But that’s my observation within my realms of experience.
Brent Ozar had a post two years ago advocating doing transaction log backups ONCE A MINUTE. The reasoning is quite interesting, and can be summed up with the concept that a theoretical database produces 60 gig of transaction log changes every hour. Would you rather back up 60 gig in one big chunk, every hour, or 1 gig every minute? Which would be faster? How big of a log would you need for one versus the other? Hopefully you’d have your log pre-sized to handle 60 gig rather than having issues with it expanding as needed.
Good things to keep in mind when scheduling log backups. Me, personally, my databases are typically fairly small. At the end of the business day I would do a final log append backup, which would then be caught by the backup system. First thing in the morning I’d do a log initialization backup and we’re back and running.
I know of a city government that installed an ERP system. Pretty big install: a couple hundred or more users on at any given time doing all sorts of different jobs. There might be utility billing jobs going on, lots of activity. This would be a good installation for a t-log backup every minute. Let’s say you do log backups every 15 minutes and your database crashes at 14 minutes since the last backup. The users have to remember what they did in those 14 minutes and double-check their work to make sure everything is current. At one minute intervals, there’s an excellent chance that almost no work was lost.
The backup interval is something that has to be negotiated with management as to how much loss of time and data is acceptable and how much they’re willing to spend to minimize those. But once a day log backups? I don’t think so.
High Altitude SQL: A 9,000' view of Microsoft SQL Server and Access 