Tail log backup

Chaitanya Kiran 696 Reputation points
2024-04-16T14:09:24.11+00:00

Are tail log backups supported only in Full Recovery model?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,737 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rahul Randive 8,521 Reputation points Microsoft Employee
    2024-04-16T14:37:06.3066667+00:00

    Hi Chaitanya Kiran

    Yes, tail log backups supported in the Full Recovery model . In the Full Recovery model, transaction log backups are taken at regular intervals, and the tail log backup captures the log records that have not yet been backed up. This ensures that you can recover all transactions up to the point of failure. In contrast, the Simple Recovery model does not support tail log backups because it truncates the transaction log after each checkpoint, making it impossible to recover transactions beyond the last checkpoint.

    It is important to note that tail log backups should only be used in specific scenarios, such as when a disaster or failure has occurred and the database needs to be restored to a specific point in time.

    Thank You!

  2. Olaf Helper 40,816 Reputation points
    2024-04-16T14:59:53.0866667+00:00

    Are tail log backups supported only in Full Recovery model?

    All log backups are only supported for databases in Full Recovery mode.

    0 comments
  3. LucyChen-MSFT 965 Reputation points Microsoft Vendor
    2024-04-17T06:42:19.0666667+00:00

    Hi @Chaitanya Kiran,

    Thanks for your information.

    From this article, we can know the Tail Log backups can be performed in:

    This article is relevant only for backup and restore of SQL Server databases that are using the full or bulk-logged recovery models.

    In that link, you can take an in-depth look at Tail Log Backup.

    This article provides the steps on how to perform Tail Log backup in SQL Server. Hope this can help you well.

    Best regards,

    Lucy Chen

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    https://docs.microsoft.com/en-us/answers/support/email-notifications

  4. LucyChen-MSFT 965 Reputation points Microsoft Vendor
    2024-04-18T08:00:52.86+00:00

    Hi @Chaitanya Kiran,

    Thanks for your information.

    I find this article describes the Tail Log Backup in detail. You can know the information from it:

    For this to be possible in Bulk logged recovery mode, no minimally logged operations must have occurred.

    I think the information resolve your issue well:

    Are they supported in Bulk logged recovery model, if log backup does not have minimally logged operations?

    Regards,

    Lucy Chen

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    https://docs.microsoft.com/en-us/answers/support/email-notifications