Why is it important to backup SQL logs?

Question

I'm a SQL noob. Whats so important about backing up the SQL log files?

Answer 1

SQL Server uses transaction logs to record all the modifications applied to the main database file(s), in order to A) provide commit/rollback tansactional capabilities and B) provide the possibility of replaying those modifications if/when a previous backup is restored and the database needs to be brought up to date to what happened since that backup was taken.

If the transaction logs are never backed up, they will grow indefinitely, continuing to record all database modifications since the last time they were backed up; so a very simple reason to back them up is, if you don't, they will keep growing and eventually fill up all the available disk space.

Another very important reason to back them up is, this is the way you do "incremental" backups in SQL Server; i.e., after a full database backup, you can take a transaction log backup (or more than one in series), which will not only truncate old log files and free disk space, but also act as a "what changed since last backup time" backup, allowing you to do incremental restores, should you need them.

Transaction logs have a very strong role in SQL Server, and are very important when coming to backup/restore.

(*) Everything above applies to databases which uses the full or bulk-logged recovery model; if a database is configured for simple recovery model, transaction logs are not used this way: a few of them are used for transactional processing, but they are kept to a minimum and automatically recycled, without growing indefinitely; incremental backups/restores are of course lost in this scenario.

Answer 2

Absolutely NO for AEP's answer. It can give a very bad impact on his business.

First of all, Log Files are not used by the Incremental backups but for Transactional Log backups. A transaction log captures the modifications made to the database in order to be able to recover the database to a consistent state in case of failure (rollback incomplete transactions)

Secondly, Differential backups also known as Incremental contain all the changes done on data pages since the last full backup. For you to understand the backup policy you need a read first about the 3 recovery models (Simple, Full, Bulk Logged) and see what is your case and what recovery option suites you best. You can read on msdn and here more about this.

There is no right answer to this question. Log backups are absolutely needed when database is set in Full or Bulk-Logged Recovery model, otherwise your Log file will grow indefinitely which can at some point claim all of your disk size.

Answer 3

Bear in mind it is only worth backing up the logs if the database is using the FULL or BULK recovery model. If your database is in SIMPLE mode then backing up the log files will have no beneficial use whatsoever.

The recovery model can be found in the Database Porperties and in the Options page.

Assuming you are using the FULL recovery model. Then you will also need to consider how often you wish to back up the transaction log files. This would be a business decision based on how much transactional data you are willing to use and therefore repeat in the event of a disaster.

Also bear in mind that a transaction log backup is completely different to a database backup and you will still need to take full backups of the database on a regular schedule.