Why is it so important to backup your transaction log?

Solution 1:

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.

Solution 2:

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.