Why is it so important to backup your transaction log?

You only have to do this if your DB Recovery Mode is set to "full". If it is set to "simple" you don't have to make a backup of the transaction log. But watch out for the difference between these two options!

First of all: If you want to be able to restore the DB to a specific point of time you have to use the "full" mode. (I think you can adjust the timing so accurate that you can even specify the milliseconds for the restore point) In "simple" mode you can only go back to the last full backup.

If you do not backup/truncate your transaction log, it will grow the whole time (in full mode). I saw databases where the .trn file was more than twice as big as the database itself. This depends on how often changes were made to the DB.

Another point is that a log backup is normally faster than a full backup.

So I think your backup plan to make a full backup every hour is not optimal. But it depends on your situation:

If you say: Okay if I can restore the DB to the last full hour, everything is alright. --> You can also think about setting the recovery mode to "simple" if you want to keep the full backup every hour.

In my opinion, a better idea would be to make a full backup in the early morning and then do a transaction log backup every hour. It should be much faster, and you are able to restore to any point of time you want to. And also your .trn file will not grow too much...

Hope this helps.

Well. You care because if you have your recovery model set to full and you don't back up the Transaction Log using SQL's backup (and not the server backup), the transaction log continues to grow until it consumes all available disk space. (I once saw a lesser colleague install SQL Server on the system drive and never back up the transaction log. It ate Windows.)

Yes, it will also restore to a specific point in time as well. Down to the minute. Like Twinkles says, yes, people dropping tables and the like.

I don't know what you're using for your hourly backup of the entire database, and if it's the same product as what you're using for the entire machine. If so, a non-SQL-aware backup solution is not supported for restores. The amount of time it takes for VSS to copy the MDF and LDF files can cause an internal timestamp mismatch, for example.

We manage several ERP systems as well. And the problem is often that at night there are often long running batch jobs which sync data with other systems. And they take sometimes an hour or more. So what you want to do in case of a crash is to jump to a point where you have consistent data. (Which means right between two batch jobs.) If you only look at the time you might not always know exactly what the status of the data base was at this time.

But of course it depends on the situation. If you don't have any automated jobs etc. you can be totally fine with an hourly backup.

There are several reasons why you want to do that:

1. A database system is usually busy, maybe doing thousands of transactions per second. The data could be spread out over several files on different filesystems. It is not trivial to make sure that the database is in a consistent (a.k.a usable) state after restoring. If your backup solution is up to the task, great, but you better be sure about this before betting your job on it.
2. An example: Somebody drops a table with important data by mistake. If you have a database backup with point-in-time recovery ability, you can restore the data quickly, without having to restore the whole system.
3. If the database is in full recovery mode, the transaction log of SQL Server will grow. Storage space in the transaction log is only reused if the transaction log has been backed up. If you do not back up the transaction log regularly, your file system will fill up until there is no space left. At which point everything will come to an immediate halt, since no new transactions can be started.

When your database grows beyond what you're able to backup in an hour, you need a different model.

A Full backup of your database will truncate your logs, but it needs to be "SQL aware", because in that scenario, it's the backup software that's telling SQL server what it has backed up, and what to truncate.

As others mention, if you have a database in the "Full" recovery model, it's transaction log will grow indefinitely, until you make a Full SQL-aware backup.

Recovery is really the issue here, not Backup. And it's not a technical decision, its a business decicion!

If your business owners are OK with losing an hour or more of their database transactions (which may be VERY difficult or impossible to redo!) then your model works. If they are OK with the system being down for hours while you restore the whole database from backup, then your model works.

However, if your business regards their ERP system as a critical asset for their operation (don't they all?), then setting a maximum acceptable recovery time (aka RTO, Recovery Time Objective) for your critical services will be a business decision.

Also, the business owners or system stakeholders need to define how much data they are willing to risk losing in an incident, aka RPO (Recovery Point Objective).

The answer if you ask them might be "NO data can be lost! The ERP system must be available 24/7/365!"... which we all know is highly unlikely to be cost-effective. If you present them with the cost associated with building such a fully redundant, non-stop system, they will come up with more reasonable figure.. ;)

The point is, if you can avoid losing any transactions, you're saving your business potentially hundreds or thousands of lost work hours. It amounts to HUGE savings in any company, and grows with the size of your company...