Sql-server – Is transaction log lost after backup on SQL Server

sql serversql-server-2008

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

Best Answer

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.

Related Question