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.