Two Methods for backing up the end log (tail of log) in the case of a disaster in SQL Server

Source: Internet
Author: User
Introduction

If the log file is not damaged due to various causes. The tail of log can be used to restore the database to the State in the event of a disaster.

For example:

. A complete backup is made in db_1 and a log backup is made at log_1 and log_2. Shortly after the log_2 backup, a fault occurred. Logs from log_2 backup to disaster occurrence. Is the end log (tail of log ). If the tail log cannot be backed up, the database can only restore to the log_2 backup point. All changes made during the tail end log are lost. For more detailed concepts, see my previous blog posts on logs.

The following describes how to back up the end log when the SQL server instance runs well and the SQL server instance crashes.

End log backup when the SQL server instance runs normally

Next we will simulate a backup of the end log of a disaster:

Now, the database testbackup has a full backup and a log backup, and the last "test data after log backup" is after the last log backup and is included in the end log.

The disk where the simulated database file is located is damaged (the log file is intact)

1. Stop the server SQL Service

2. delete database files (MDF files)

In this case, access to the database testbackup in SSMs will become unavailable:

At this point, because the SQL server instance is available, tail logs are backed up by specifying the no_truncate option in the T-SQL Statement (which must be specified, otherwise tail logs cannot be backed up:

Complete Backup Recovery and two transaction log recovery are performed in turn. We can see that the data has been successfully restored to a disaster point:

 

End log backup when the SQL server instance crashes

At this point, for various reasons, the SQL server instance also crashes and cannot back up the end log through the T-SQL. In this case, the database file is corrupted, and the transaction log file remains correct.

Assume that, as in the preceding example, I have a full backup (testbackup_full.bak), a log backup (testbackup_log1.bak), and a log file (LDF ).

At this time, I copied these files to other machines with SQL Server instances.

Create a database with the same name as the original database. Set to offline:

Delete the MDF file of the new database.

Replace the log file of the database to be backed up with the original LDF file.

At this time, the end log is backed up directly, successfully:

The original SQL server instance restores the full backup and two log backups once after recovery. Disaster recovery is successful.

 

Summary

I believe I can see this articleArticlePeople do not want to use the above two methods. However, Murphy's Law (if something gets worse, no matter how small it is, it will happen) is cruel, exercise in advance is always more comfortable than practice using production data in real situations :-)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.