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.
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
Sounds good so far. You must remember to test your backups as well!
If your system becomes critical, you can also consider transaction log shipping, or more frequent intervals than 3 hours.
Optimal SQL Server backup strategy depends from your database availability and data consistency requirements only...
If you are ready to lost your data for 6 hours (period between consecutive transaction log/database backups) - it's ok. If not - think more about optimizing backup strategy (e.g. with more frequent transaction log backups/incremental backups, even database mirroring).
andy,
you may also look to configure some index maintenance on the database as well. You can configure a separate maintenance plan for those activities, perhaps run them nightly or weekly when users are not connected.
The way you have your backup strategy if fine, your restore window is every three hours. As long as you can live with losing three hours of data at the absolute worse possible time, everything is fine.