Applies to
ApexSQL Log, ApexSQL Recover
Summary
A tutorial on how to use transaction log “chains” to provide sufficient data sources for successful disaster recovery, transaction rollback or database auditing, effectively avoiding a large singular log file.
Description
Transaction log backups are required to provide a more continuous data stream, to allow more complete recovery in case of inadvertent data loss and also provide vital transactional information, not found in regular database backups.
Transaction log backups not only provide successful auditing and recovery, but also enable the backed up log records to be removed from the online transaction log file. The new transactions overwrite the old ones, thus preventing the transaction log file from growing. If you do not back up the transaction log frequently enough, it can affect the database performance when its size becomes too large.
What is the best way to create transaction log backups?
We recommend creating a chain of transaction log backups, by scheduling a log backup to be taken at a regular interval after a full backup has been taken.
What is a transaction log chain?
A transaction log chain is a continuous sequence of transaction log backups. It starts with a full database backup followed by subsequent log backups up until the recovery/auditing point.
A log chain enables you to audit/recover/rollback the transactions faster using several transaction log backup files instead of a single online transaction log which can be multiple times larger than when transaction log backups are used.
How to create a log chain?
- Check the database recovery model. If it’s the Simple model, switch it to the Full recovery model.
- Create a full database backup.
- Create transaction log backups regularly.
Your chain must remain unbroken
If a chain becomes broken, only the transactions in the logs up to the last backup prior to when the interruption occurred can be shown.
How often should I create transaction log backups?
It depends on your environment, your needs, and how much data you are willing to lose. Taking a transaction log backup every 15 to 30 minutes is usually enough for high transaction databases. Keeping in mind that if you take one only every 30 minutes, you may lose 30 minutes of data should a disaster occur.
How often should I create full database backups?
Creating a full database backup once a day is sufficient in most cases. By creating a new full database backup, you can start a new log chain, thus reducing the number of files that are used for transaction analysis/recovery and hence speeding up the recovery process.
Last updated
June 24, 2014
