How do I schedule regular transaction Log Backups?

Applies To All Versions

It can be important to schedule regular backups of your transaction log. This will ensure that at regular intervals the transaction log is emptied of uneccessary items, preventing the log from growing to infinite size and tying up server resources.

First, determine your database's Recovery Model settings. This is most commonly set to FULL or to SIMPLE. To identify this, right click on the database in SQL Management Studio and choose 'Properties' ---> 'Options'.


If your intended database recovery method will be to restore from the most recent database backup (and database backups are being performed regularly) then the recommended recovery method is SIMPLE. SIMPLE will limit log growth as many unneeded entries can be recycled without growing the log. You should still monitor the log file size on a regular basis to ensure it is not growing significantly.

If your intended recovery method includes restoring the database to a point in time between scheduled database backups you should choose FULL. Databases with Full Recovery Model must have scheduled regular database maintenance which includes transaction log backups. The following MSDN article details how to configure a transaction log backup as part of your regularly scheduled database backups.

Additional resources on the necessity of Transaction Log backups:

SQL Recovery Models Explained
Database Maintenance Best Practices
Huge Log Files

Have more questions? Submit a request


Article is closed for comments.