Is transaction log lost after backup on SQL Server?

Question

I have database that has regular nightly backup (full recovery, full backup). I noticed some irregularities in data and wanted to check out transaction log to see what happened. I restored earlier backup on my developer machine and noticed transaction log is empty. So ...

Transaction log is not written along with full backup ?

Do I lose transaction log on a database when I do full backup ( is log truncated on full backup ) ?

If I want to have data backup and transaction logs backup what should I do ?

Current backup script:

BACKUP DATABASE MyDB TO  
DISK = @MyFileName 
WITH NOFORMAT, INIT,  NAME = N'MyDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,   
STATS = 10

Answer 1

Transaction log is not written along with full backup ?

This is not correct. In FULL recovery mode – the database backup contains all the log necessary to make the restored database transactionally consistent as of the end of the backup operation.

Refer myths around full database backups from Paul Randal. Below is an excerpt

*A full database backup, when restored either explicitly using WITH RECOVERY, or when a recovery option is not specified ALWAYS results in a transactionally-consistent database. The point-in-time at which the database is restored to is the point at which the data-portion-reading part of the backup operation completed. All database backups include transaction log, otherwise there would be no way to rollback transactions that were active at the time the data-reading portion of the backup ended.*

Do I lose transaction log on a database when I do full backup ( is log truncated on full backup ) ?

No you do not loose T-Log when you do full database backup. Refer to

Misconceptions around the log and log backups: how to convince yourself for understanding how log backups work.

If I want to have data backup and transaction logs backup what should I do ?

If you want to have data backup and T-log backups, you have to first perform FULL database backup and then take subsequent T-log backups. So when you restore your database, you can restore a full backup and then subsequent T-log backups to do a Point-in-Time recovery.

Answer 2

This great post by Paul Randal should give you better idea about logs. But gist of it is, when you do full backup it doesn't mean that you will have all of your changes included in it plus it also doesn't means that it will clear the log.

And if I understand Backup/Restore correctly, it is valid that t-log will be empty when you restore database because when you do full backup it will flush dirty pages from log and write it to actual data file (given that transaction is completed) and you didn't took log backup so when you restore that FULL backup only, you should see only transactions that were written to database but not the one that were in t-log.

Answer 3

Databases will typically issue a checkpoint prior to dumping the data to ensure all commits are written to storage. The checkpoint can cause the database to truncate the transaction log, depending on database settings.

According to Microsoft, the following conditions will automatically trigger log truncation:

•Under the simple recovery model, after a checkpoint.

•Under the full recovery model or bulk-logged recovery model, if a checkpoint has occurred since the previous backup, truncation occurs after a log backup (unless it is a copy-only log backup).

The transaction log can be dumped indedpendantly of the database using the BACKUP LOG command:

Backing Up the Transaction Log (full and bulk-logged recovery models)

BACKUP LOG { database_name | @database_name_var } 
TO <backup_device> [ ,...n ] 
[ <MIRROR TO clause> ] [ next-mirror-to ]
[ WITH { <general_WITH_options> | <log-specific_optionspec> } [ ,...n ] ]
[;]