SQL Server中的尾日志备份和还原

A tail-log backup is a special type of transaction log backup. In this type of backup, the log records that have not been backed up are captured. This prevents data loss and maintains the log chain sequence.

尾日志备份是事务日志备份的一种特殊类型。 在这种类型的备份中，将捕获尚未备份的日志记录。 这样可以防止数据丢失并维护日志链顺序。

The following are the Tail-log backup scenarios

以下是尾日志备份方案

• The tail-log backup process is a common scenario where we intend to migrate very large databases within a short outage window. For example, if you were planning to shut off a database at one location, and then restore it on a different server, the last thing you would want to do before you shut off the database is to initiate a tail log backup. The term, “tail” implies that it is the end of the log backup sequence. In this way, we ensure that there is zero data loss and that a chain for the restore operation is maintained. In case we don’t initiate a tail-log backup, as we would end up with data los, we would lose all the transactions that happened after the last log backup.
  尾日志备份过程是一种常见的情况，我们打算在一个短暂的中断窗口内迁移非常大的数据库。 例如，如果您打算在一个位置关闭数据库，然后将其还原到另一台服务器上，那么在关闭数据库之前，您要做的最后一件事就是启动尾日志备份。 术语“尾巴”表示这是日志备份序列的结尾。 这样，我们确保数据丢失为零，并确保恢复操作的链得以维持。 如果我们不启动尾日志备份，因为最终会导致数据丢失，那么我们将丢失上次日志备份后发生的所有事务。
• If the database fails to start or goes offline, in such cases we may want to restore the database immediately. In the middle of such an incident, the database administrator’s job is to safeguard the database and to bring the database up and running as quickly as possible. It’s easy to start the recovery and start restoring the database backups. However, before starting the recovery, there is an intermediate step that one has to perform and the process is known as tail-log backup. It is initiated regardless of when the last t-log backup was made.
  如果数据库无法启动或脱机，则在这种情况下，我们可能要立即还原数据库。 在此类事件中，数据库管理员的工作是保护数据库并尽快使数据库启动并运行。 开始恢复和开始还原数据库备份很容易。 但是，在开始恢复之前，必须执行一个中间步骤，该过程称为尾日志备份。 无论何时进行上一次t-log备份，都将启动该备份。
• When the database is damaged or corrupted, try to initiate a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option of the BACKUP statement.
  当数据库损坏或损坏时，请尝试使用BACKUP语句的WITH CONTINUE_AFTER_ERROR选项启动尾日志备份。

The NORECOVERY and CONTINUE_AFTER_ERROR are the backup log clauses that can be used with Tail-log backup scenarios

NORECOVERY和CONTINUE_AFTER_ERROR是可以与尾日志备份方案一起使用的备份日志子句

入门 (Getting started)

Let’s now deep-dive into the log options and backup and recovery scenarios of tail-log backup.

现在，让我们深入研究尾日志备份的日志选项以及备份和恢复方案。

To demonstrate the workings of the tail log backup, let’s create a database SQLShackTailLogDB and set the recovery model of the database to FULL.

为了演示尾部日志备份的工作原理，让我们创建一个数据库SQLShackTailLogDB并将数据库的恢复模型设置为FULL。

-- create a new database  SQLShackDSDemo 
USE MASTER
GO
DROP DATABASE IF EXISTS SQLShackTailLogDB 
GO
CREATE DATABASE SQLShackTailLogDB
ON   
( NAME = SQLShackTailLogDB_dat,  
    FILENAME = 'f:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\SQLShackTailLogDB_dat.mdf',  
    SIZE = 50,  
    MAXSIZE = 150,  
    FILEGROWTH = 10 )  
LOG ON  
( NAME = SQLShackTailLogDB_log,  
    FILENAME = 'f:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\SQLShackTailLogDB_log.ldf',  
    SIZE = 15MB,  
    MAXSIZE = 50MB,  
    FILEGROWTH = 10MB ) ;  
GO  
 
ALTER DATABASE SQLShackTailLogDB SET RECOVERY FULL
GO

Now, create a table SQLShackAuthor, and add some data to it. You can see that the table is populated with 5 rows.

现在，创建一个表SQLShackAuthor ，并向其中添加一些数据。 您可以看到该表填充了5行。

USE SQLShackTailLogDB;
GO
 
-- create a table SQLShackAuthor
CREATE TABLE SQLShackAuthor (
    AuthorID INT IDENTITY(1,1) PRIMARY KEY,
    authorName varchar(100)
);
GO
INSERT SQLShackAuthor (authorName) VALUES ('Brain Lockwood'),('Ahmad Yaseen'),('Samir Behara'),('Luna Cvetovic'),('Prashanth Jayaram')
GO

Let’s select everything from the SQLShackAuthor table

让我们从SQLShackAuthor表中选择所有内容

SELECT * FROM SQLShackAuthor;
GO

In this section, we will discuss the creation of the backup of the SQLShackTailLogDB database. The following backup command is initiated and the backup file SQLShackTailLogDB_FULL.bak is written to the disk. Now, the backup file includes these 5 records that we just added.

在本节中，我们将讨论创建SQLShackTailLogDB数据库的备份。 启动以下备份命令，并将备份文件SQLShackTailLogDB_FULL.bak写入磁盘。 现在，备份文件包括我们刚刚添加的这5条记录。

-- create a full backup
BACKUP DATABASE SQLShackTailLogDB
TO DISK = 'f:\PowerSQL\SQLShackTailLogDB_FULL.bak' with FORMAT, INIT, COMPRESSION