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

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 :-)