locked
Tail log backup RRS feed

  • Question

  • Can we take tail log backup if log file goes missing?

    Kiran

    Tuesday, June 16, 2020 3:26 PM

Answers

  • Missing log backups do not prevent making additional log backups. But with them, you will not be able to use any log backup taken after the earliest missing file. 

    What question were you answering?

    A tail-log bakup, as I understand it, is a log backup with the NOTRUNACATE option. This is a backup you can take if the mdf file goes belly up. Backing up the tail of the log permits you restore the database without data loss.

    But if the log file is gone, it is of course not possibly to backup the transaction log, so the answer to Kiran's question is simply: No.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by TiborKMVP Wednesday, June 17, 2020 6:37 AM
    • Marked as answer by juniorkiran Wednesday, June 17, 2020 1:29 PM
    Tuesday, June 16, 2020 9:41 PM
  • Hi juniorkiran,

    >Can we take tail log backup if log file goes missing?

    No. On a damaged database backing up the tail of the log can succeed only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.And tail log backups capture the tail of the log even if the database is offline, damaged, or missing data files.

    Please reference: tail-log-backups-sql-server
    You can also reference Erland Sommarskog's reply.


    Best Regards.

    myx


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by juniorkiran Wednesday, June 17, 2020 1:30 PM
    Wednesday, June 17, 2020 1:58 AM

All replies

  • Missing log backups do not prevent making additional log backups. But with them, you will not be able to use any log backup taken after the earliest missing file. 
    Tuesday, June 16, 2020 4:16 PM
    Answerer
  • If Sql Server cannot locate the log file for a database, it won't even be available and will most likely be marked suspect. Is this what you mean?
    Tuesday, June 16, 2020 4:17 PM
  • When your database is online after taking the backup, there may be more transactional operations happen. This means when we try to restore the database, it may be possible that we may lose some transactions and often you also see that error that your database should take the tail-log backup before restoring the database.

    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Tuesday, June 16, 2020 5:41 PM
  • Missing log backups do not prevent making additional log backups. But with them, you will not be able to use any log backup taken after the earliest missing file. 

    What question were you answering?

    A tail-log bakup, as I understand it, is a log backup with the NOTRUNACATE option. This is a backup you can take if the mdf file goes belly up. Backing up the tail of the log permits you restore the database without data loss.

    But if the log file is gone, it is of course not possibly to backup the transaction log, so the answer to Kiran's question is simply: No.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by TiborKMVP Wednesday, June 17, 2020 6:37 AM
    • Marked as answer by juniorkiran Wednesday, June 17, 2020 1:29 PM
    Tuesday, June 16, 2020 9:41 PM
  • Hi juniorkiran,

    >Can we take tail log backup if log file goes missing?

    No. On a damaged database backing up the tail of the log can succeed only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.And tail log backups capture the tail of the log even if the database is offline, damaged, or missing data files.

    Please reference: tail-log-backups-sql-server
    You can also reference Erland Sommarskog's reply.


    Best Regards.

    myx


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by juniorkiran Wednesday, June 17, 2020 1:30 PM
    Wednesday, June 17, 2020 1:58 AM
  • Yes, thats what I mean. Can we take tail log backup in such scenario?

    Kiran

    Wednesday, June 17, 2020 1:28 PM
  • Yes, thats what I mean. Can we take tail log backup in such scenario?

    Again: no.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, June 17, 2020 9:40 PM