I'm primarily a developer, but for one small in-house system I'm also currently in charge of some of the configuration of a server running SQL server enterprise edition.
The database itself sees activity from two sources:
- A piece of client software that delivers data
- A web app that our customer uses to check on the data from the clients and manage things externally.
I'm reasonably happy using the built-in SQL server backup utilities, I've set up a maintanence plan that backs up the whole database every 6 hours, and the transaction logs every 6 hours in between the database backups (e.g. 6am, database backup, 9am transaction log backup, 12pm database backup, 3pm transaction log backup). We take a tape-snapshot of the whole server every week (the data isn't mission critical, more of an R&D project).
Before this was set up the database was beginning to grow dramatically, now all of the full database backups come in at a very manageable 15Mb (and slowly growing), and the transaction logs between 9 and 12Mb.
Am I doing it right? Do you have any other insight into frequency of transaction log backup?
If there's anything in another question that I've missed that might be useful, please feel free to point me to it.
Best Answer
Frankly, there's no reason NOT to run Transaction Log backups at a much more frequent rate. A transaction log backup doesn't HURT anything. (In MOST scenarios it's just going to take a bit of CPU and disk activity - but it's typically almost negligible.)
Moreover, you can both increase your coverage AND boost performance by moving log-file backups to less performant disk: SQL Server Magazing: Maximize Storage Performance
By increasing frequency of your backups, you'll decrease your window of potentially lost data. (Technically, if SQL Server crashes you can sometimes recover lost transactions since the last FULL/DIFFERNTIAL backup IF your log file is still intact. But usually, if something has gone wrong enough for you to be backing up in the first place, there's a DECENT chance your log file is gone/toast.)
Feel free to check out the following videos for a bit more background and information on what's going on in terms of backups, logs, and best practices:
SQL Server Backups Demystified
SQL Server Logging Essentials
Managing SQL Server 2005/2008 Log Files
SQL Server Backup Best Practices