SQL Server Backups «

If you are new (or used to SQL 2000) SQL 2005, you may notice that after your first full backup, your transaction logs aren’t clearing. I ran into this problem today with one of our SQL 2005 servers. When you do a full backup in SQL 2005, you are only backing up the actual database, not the log files. In order to actually backup the SQL logs for a given db, you need to actually run a separate “Log Backup” job. This will actually backup the logs, and then truncate them, keeping your log files from getting to large. You can backup the logs multiple times in between full backups, say if you are a bank and need to backup logs every 5 minutes. You will need to backup jobs in say Backup Exec to perform this type of backup scheme.

Another type of backup is a “Simple Backup” which actually backups up the db, commits the Transaction logs, and is roughly equivalent to the “Full” that you think of in SQL 2000, (or Exchange) with the logs flushing and maintaining a manageable logfile size. Know though, that when you do simple backup you can only restore a full backup, and can’t “roll back” your logs.

Here is a thread with more info on technet, and another resource on technet explaining this in more detail.

We ended up choosing simple, as this is the most hands off approach, and we only ever have a need to restore to the last full backup. You can change from Full to Simple in the options section of an individual database’s properties.