The confusion over SQL Server backups

Something occured to me the other day... the collective wisdom of the internet isn't always a good thing, especially when dealing with something that has many people confused!

I had a moment of self doubt recently, brought on by the need to explain SQL backups to someone. I wanted to make sure that what I thought was correct really was, rather than simply soldier on and risk passing on a poor understanding to an unknowing recipient. What I found though were a lot of people who think they understand SQL backups, but who clearly don't!

So what is it they don't get you ask?

Well generally speaking the connection between different type of backup, and their effect on the transaction log. I saw people thinking that a full backup would cause old transaction log records to be cleared, and other people thinking that differential backups use the transaction log for its data... both of which are wrong. So to be clear...

Full Backups - Only the database (.mdf) itself is backed up*, the transaction log isn't touched, and it will continue to grow as always.

Differential Backups - Only the database (.mdf) itself is backed up, by backing up those extents within the database which are marked as having changed since the last Full backup. The transaction log isn't touched.

Transaction Log Backups - Finally the transaction log file is used, and the completed transactions marked as such so the space can be re-used / cleared, or more accurately, the inactive portion of the log is truncated.

So the critical thing here is that if you have a database in Full or Bulk-logged recovery mode, you must have a transaction log backup done at some point, even if you don't plan to use it. Otherwise the transaction log will never be truncated, and it will just continue to grow until it runs out of space. Or of course, consider simply changing the recover mode to simple.

For a couple of good guides explaining it check out
http://www.teratrax.com/articles/sql_server_backup_types.html

and

http://www.sqlservercentral.com/articles/64582/

* yes I know that the transactions made during the process of the backup are then backed up from the transaction log at the end, but lets not confuse matters here.