3

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
1
  • 1
    Transaction logs can be dumped inedependently of the database. In Sybase ASE the command is dump tran, but I'm not sure of the SQLServer syntax. Apr 23, 2013 at 10:50

3 Answers 3

4

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.

5
  • Just a point of order, the behavior described in this answer isn't just restricted to FULL recovery mode, but works the same in SIMPLE and BULKLOGGED.
    – Mike Fal
    Apr 23, 2013 at 16:50
  • Answer to first question is not correct.
    – IvanP
    Apr 23, 2013 at 17:32
  • @IvanP .. if you read the reference that I pointed out ..it clearly says .. 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 .. why do you feel that it is not correct ?
    – Kin Shah
    Apr 23, 2013 at 19:02
  • It's perhaps a bit incomplete/misleading given the context of the question. The transaction log that's backed up with a full backup is only enough to make the backup transactionally consistent -- it's not the same information as would be captured by a log backup, which I think is what the question is getting at.
    – Jon Seigel
    Apr 24, 2013 at 2:23
  • @JonSeigel It's completely misleading. Given that I am asking from angle of developer and not database admin, and that I need log to debug what happened, correct answer is: transaction log is NOT written. And in fact it is not. That small part that is written is irrelevant and should not be mentioned as it is only confusing.
    – IvanP
    Apr 24, 2013 at 12:15
1

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.

1
  • Yes, the transaction log is empty after restore. I don't know why I expected full backup to transfer transaction log as well. When I saw size of transaction log I realized how wrong I was...
    – IvanP
    Apr 24, 2013 at 12:21
0

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 ] ]
[;]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.