locked
Does a full DB backup include the tlog? RRS feed

  • Question

  • When a full DB backup is performed, does this include the tlog as well?  Normally tlog backups are done in addition and more frequently to a full DB backup I believe.  Ive not had to mess with backups much in the past, but I am now.

    Reason being, is that for a SQL server I inherited, there appears to be a maintence plan setup that does a full backup already, but there are no seperate tlog backups being done.  Can I do a tlog backup to the existing .bak file that the maintence plan is using?

    • Edited by shiftbit Thursday, January 9, 2014 2:20 AM sdfgf
    Thursday, January 9, 2014 2:18 AM

Answers

All replies

  • Full backup doesnt include TLOG backup. You should separately take a TLOG backup. But before that you should check the recovery model of the database. TLOG backup can't be taken if the database is in SIMPLE recovery model.
    Thursday, January 9, 2014 2:25 AM
  • Tlog backups are the backup of the transaction logs since the last full backup or a another tlog backup. So in order to achieve a recovery to a point in time you would need a full back and all tlog backups before failure. 

    But how often you need the tlog backup, depends on your systems criticality.  


    Satheesh
    My Blog


    Thursday, January 9, 2014 2:28 AM
  • No - it definitely doesn't. The only thing that allows the log to clear/truncate in the FULL or BULK_LOGGED recovery models is a log backup - no exceptions.

    Also refer paul's blog he explained with example.

    http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/

    http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

    http://technet.microsoft.com/en-us/library/ms191429.aspx

    Thursday, January 9, 2014 3:17 AM
  • full database backups do contains all the log necessary to make the restored database transactionally consistent as of the end of the backup operation.

    http://www.sqlskills.com/blogs/paul/debunking-a-couple-of-myths-around-full-database-backups/

    http://www.sqlskills.com/blogs/paul/more-on-how-much-transaction-log-a-full-backup-includes/

    but if your database is in full or bulk_logged recovery models you need to take tlog backups to achieve point-in-time recovery and  to release space occupied by transaction logs on disk.

    you can refer below article to know more about backups.

    Understanding SQL Server Backups 


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page


    • Edited by Praveen Rayan D'sa Thursday, January 9, 2014 5:36 AM
    • Proposed as answer by Shanky_621MVP Thursday, January 9, 2014 5:43 AM
    • Marked as answer by Fanny Liu Wednesday, January 15, 2014 1:09 AM
    Thursday, January 9, 2014 5:33 AM
  • Hello,

    Only Praveen's answer is correct.Paul has written beautiful article stating that Full backup does include some transaction log .Suppose a change to a page is made after it has been read by backup thread and that change is committed now backup thread is not going to read that page again so if trn log is not included that this operation committed after page  was read you will get inconsistent backup.So if required full backup has some amount of trn log backup


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, January 9, 2014 5:47 AM
  • Hi

    What do you think, a full backup started at 8AM and completed at 9:30AM, what is the database consistency to 8 or 9:30am? If you know the correct answer  you will understand "does this include the tlog as well?".


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, January 9, 2014 8:00 AM
    Answerer