sql server伪列_伪简单SQL Server恢复模型

sql server伪列

This article gives an overview of the Pseudo Simple SQL Server Recovery Model. It also explores the conditions in which the database behaves in Pseudo mode.

本文概述了伪简单SQL Server恢复模型。 它还探讨了数据库在伪模式下的行为情况。

SQL Server中的恢复模型类型 (Types of Recovery model in SQL Server)

Recovery model property in the SQL Server database controls the transactions logging, supported backup types and database recovery scenarios such as point-in-time recovery. We have the following three recovery models for a SQL database.

SQL Server数据库中的恢复模型属性控制事务日志记录，受支持的备份类型和数据库恢复方案，例如时间点恢复。 对于SQL数据库，我们具有以下三种恢复模型。

• Full: We can perform point-in-time recovery and recover data without any data loss. It supports available backups such as full, log, differential 完整 ：我们可以执行时间点恢复并恢复数据，而不会丢失任何数据。 它支持可用备份，例如完整，日志，差异备份
• Bulk-logged: It logs minimum log information for the bulk transactions such as BCP, CREATETEXT, and WRITETEXT. It works similar to a full recovery model and supports all available backups except that we cannot have a point in time recovery for the database in this recovery model 批量记录：它记录有关BCP，CREATETEXT和WRITETEXT等批量事务的最少日志信息。 它的工作方式类似于完整恢复模型，并支持所有可用的备份，但在该恢复模型中我们无法对数据库进行时间点恢复
• Simple: It is the simplest form of a recovery model and truncates the logs once the transaction is committed. We cannot do point in time recovery in this as it does not supports log backups 简单：这是恢复模型的最简单形式，一旦提交事务，它就会截断日志。 我们无法进行时间点恢复，因为它不支持日志备份

You can refer article Understanding SQL Server database recovery models for detailed information about the recovery models.

您可以参考文章了解SQL Server数据库恢复模型，以获取有关恢复模型的详细信息。

伪简单SQL Server恢复模型 (Pseudo-Simple SQL Server Recovery Model)

You might wonder that initially, we mentioned only three recovery models, but as per the name, it looks like a different recovery model. We will get to know this recovery model in the latter part of the article.

您可能想知道，最初我们只提到了三种恢复模型，但是顾名思义，它看起来像是一种不同的恢复模型。 我们将在本文的后半部分了解这种恢复模型。

Let’s create a database:

让我们创建一个数据库：

CREATE DATABASE RecoveryModel;

It creates a copy of the model database, and we can use sys.databases to check the current recovery model of this database.

它创建了模型数据库的副本，我们可以使用sys.databases检查该数据库的当前恢复模型。

SELECT name, 
    recovery_model_desc
FROM sys.databases
WHERE name = 'RecoveryModel';

As you can see the database recovery model is FULL. In the full recovery model, it should truncate the logs after log backups only.

如您所见，数据库恢复模型为FULL。 在完全恢复模型中，它应该仅在日志备份后截断日志。

Let’s execute a sample workload and see do we have a database in full SQL Server Recovery Model.

让我们执行一个示例工作负载，看看我们是否具有完整SQL Server恢复模型数据库。

Let’s create a table and insert few records in it.

让我们创建一个表并在其中插入一些记录。

CREATE TABLE test(id INT);
GO 
INSERT INTO test
VALUES(1);
GO 1000

In the Full recovery model, it should wait for the transaction log backup before the truncation of the transaction log. We can check the status of the log reuse wait using the following query.

在完全恢复模型中，它应在事务日志被截断之前等待事务日志备份。 我们可以使用以下查询来检查日志重用等待的状态。

SELECT name, 
    recovery_model_desc, 
    log_reuse_wait_desc
FROM sys.databases
WHERE name = 'RecoveryModel';

In the following screenshot, we can see a full recovery model, but log_reuse_wait_desc shows NOTHING. Nothing shows that the database does not require log backups to truncate the logs. The database is behaving as a simple recovery model in which it truncates the logs after the transaction.

在以下屏幕截图中，我们可以看到完整的恢复模型，但是log_reuse_wait_desc显示为NOTHING。 没有任何信息表明数据库不需要日志备份来截断日志。 数据库的行为就像一个简单的恢复模型，在该模型中，交易后它会截断日志。

The database should meet the following conditions in the full SQL Server Recovery Model:

在完整SQL Server恢复模型中，数据库应满足以下条件：

• The database should have a full backup to start an LSN chain
  数据库应具有完整备份以启动LSN链
• Database recovery model should be full
  数据库恢复模型应已满

Execute the following query, and it returns whether the database is really in the full recovery model or not. The following query performs a check as per the following:

执行以下查询，并返回数据库是否真正处于完全恢复模型中。 以下查询根据以下内容执行检查：

• If the database recovery model is full, does it have a full backup to validate the LSN chain?
  如果数据库恢复模型已满，是否具有完整的备份来验证LSN链？
• If the database recovery model is full without a full backup for the LSN chain, it shows output that the database is not behaving like a full recovery model. This condition of the recovery model is known as the pseudo-simple recovery model
  如果数据库恢复模型已满，而LSN链没有完整备份，则会显示输出，表明数据库的行为不像完整恢复模型。 恢复模型的这种情况称为伪简单恢复模型

In this query, we use tow system tables and views:

在此查询中，我们使用两个系统表和视图：

• Sys.database_recovery_status to get details of the last log backup LSN. If the database does not have a full backup, it shows NULL value else, and it will be the LSN of the full backup
  Sys.database_recovery_status获取上一次日志备份LSN的详细信息。 如果数据库没有完整备份，则显示NULL值，否则它将是完整备份的LSN。
• We use Sys.databases command to check the database recovery model
  我们使用Sys.databases命令检查数据库恢复模型

查询以检查SQL Server数据库的实际行为 (Query to check actual behavior of a SQL Server database)

Execute the following query, and it gives the message whether database behaving similar to a database in full recovery model or not.

执行以下查询，它会提示数据库是否与完全恢复模式下的数据库相似。

DECLARE @IsReallyFull BIT= 0;
DECLARE @LastLogBackupLSN NUMERIC(25, 0);
DECLARE @RecoveryModel TINYINT;
SELECT @LastLogBackupLSN = [last_log_backup_lsn]
FROM sys.database_recovery_status
WHERE [database_id] = DB_ID('RecoveryModel');
SELECT @RecoveryModel = [recovery_model]
FROM sys.databases
WHERE [database_id] = DB_ID('RecoveryModel');
SELECT CASE
        WHEN @RecoveryModel = 3
        THEN 'Database is in Simple recovery model'
        WHEN @RecoveryModel = 2
        THEN 'Database is in Bulk-logged recovery model'
        WHEN @RecoveryModel = 1
            AND @LastLogBackupLSN IS NOT NULL
        THEN 'Database is really in Full recovery model'
        WHEN @RecoveryModel = 1
            AND @LastLogBackupLSN IS NULL
        THEN 'Database is in Pseudo simple recovery model'
    END AS Recoverymodel;

In the output, you can see the database is not having similar to a full recovery model database.

在输出中，您可以看到该数据库与完整恢复模型数据库没有相似之处。

Let’s understand the substantial term log sequence number and will come back to this part again.

让我们了解实质性术语对数序列号，并将再次回到这一部分。

日志序号 (Log sequence number)

Execute the following query to check the last log backup LSN, and we get NULL value in it.

执行以下查询以检查最后一个日志备份LSN，然后在其中获得NULL值。

SELECT d.Name, 
    d.recovery_model_desc, 
    dr.last_log_backup_lsn
FROM sys.databases d
  INNER JOIN sys.database_recovery_status dr ON d.database_id = dr.database_id
WHERE d.database_id = DB_ID('Recoverymodel');

SQL Server backup internally works on the log sequence number. We might have different kinds of backups full, differential and log backup for the database in full recovery mode. All these backups are interconnected using the LSN’s. We require a full backup to start a backup chain, and subsequent backups follow this LSN chain.

SQL Server备份在内部使用日志序列号。 对于完全恢复模式的数据库，我们可能有不同类型的完整备份，差异备份和日志备份。 所有这些备份都使用LSN互连。 我们需要完整备份才能启动备份链，随后的备份也遵循该LSN链。

In the following screenshot, we see the database in full SQL Server Recovery Model and having a log backup chain that follows the backups in a sequence full, the transaction log and differential backup.

在下面的屏幕快照中，我们看到数据库处于完整SQL Server恢复模型中，并且具有一个日志备份链，该日志备份链按照完整备份，事务日志和差异备份的顺序进行备份。

Let’s take full database backup using the following query, and it starts the backup chain for the full recovery model.

让我们使用以下查询进行完整的数据库备份，然后启动完整恢复模型的备份链。

BACKUP DATABASE [RecoveryModel] TO  DISK = N'E:\Backup\RecoveryModel.bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Once the full backup command, execute the command to check the last log backup LSN. Previously we have a NULL value, but now we can see LSN number in this column.

完成完整备份命令后，执行命令以检查最后一个日志备份LSN。 以前我们有一个NULL值，但是现在我们可以在此列中看到LSN号。

Let’s execute the workload on this database and check the log holding reason. It shows the reason Log_Backup that means log will be truncated only after the log backup.

让我们在此数据库上执行工作负载并检查日志保存原因。 它显示了Log_Backup的原因，这意味着仅在备份日志后日志才会被截断。

Execute the Query to check the actual behavior of a SQL Server database. In the output, we get the message that the database is really in full recovery mode.

执行查询以检查SQL Server数据库的实际行为。 在输出中，我们收到消息，表明数据库实际上处于完全恢复模式。

Let’s switch the recovery model from FULL to Simple and do the transactions and revert to Full again. Once we change the SQL Server Recovery Model to Simple, it breaks the log chain. We changed the recovery model back to full, but it does not recreate the LSN chain. It’s required to take another full backup, so that new backup chain can be formed, and you can start subsequent log backups. The database behavior during the log chain break scenario is called the pseudo-simple recovery model. In this model, the database behaves similar to a simple SQL Server Recovery Model and truncates the logs once the transaction is committed. We need to take a full backup so that SQL Server can prepare the log chain and subsequent backup can work.

让我们将恢复模式从“完整”切换为“简单”，然后执行事务并再次恢复为“完整”。 一旦将SQL Server恢复模型更改为“简单”，它就会中断日志链。 我们将恢复模型改回了完整模型，但是它没有重新创建LSN链。 需要执行另一个完整备份，以便可以形成新的备份链，并且可以开始后续的日志备份。 日志链中断场景期间的数据库行为称为伪简单恢复模型。 在此模型中，数据库的行为类似于简单SQL Server恢复模型，并在提交事务后截断日志。 我们需要进行完整备份，以便SQL Server可以准备日志链并可以进行后续备份。

In the article, Understanding Log Sequence Numbers for SQL Server Transaction Log Backups and Full Backups, we explored the log sequence number and its relation with the log backup and full backups. Until we explored that if we change the database recovery model from full to simple, it breaks the LSN chain.

在“ 了解SQL Server事务日志备份和完整备份的日志序列号”一文中，我们探讨了日志序列号及其与日志备份和完整备份的关系。 直到我们探索到，如果我们将数据库恢复模型从完全恢复模式更改为简单模式，就会破坏LSN链。

We can also change the recovery model from bulk-logged to simple SQL Server Recovery Model. It also breaks the LSN chain and works similar to a Pseudo simple recovery model database.

我们还可以将恢复模型从大容量日志记录更改为简单SQL Server恢复模型。 它还打破了LSN链，并且类似于伪简单恢复模型数据库那样工作。

Should you worry about if a database is in a Pseudo simple recovery model? Yes, it should be a concern as it affects the database recovery. You cannot perform point in time recovery because log backups will not work once the LSN chain breaks. Ideally, you should never change the database recovery model from full to simple. Usually, dba changes the recovery model to avoid excessive log growth during certain operations, but it is not recommended. You should plan the frequency of transaction log backups in such a way that it does not increase the huge log space.

您是否应该担心数据库是否处于伪简单恢复模型中？ 是的，应该担心，因为它会影响数据库的恢复。 您无法执行时间点恢复，因为一旦LSN链中断，日志备份将无法工作。 理想情况下，永远不要将数据库恢复模型从完全更改为简单。 通常，dba会更改恢复模型，以避免在某些操作过程中日志过多增长，但是不建议这样做。 您应该以不增加大量日志空间的方式规划事务日志备份的频率。

结论 (Conclusion)

In this article, we explored the scenario in which a database can behave like a pseudo-simple SQL Server Recovery Model. You should avoid the recovery model change from full to simple. If you have to do it, please take a full database backup immediately to set up an LSN chain for the subsequent backups.

在本文中，我们探讨了数据库可以像伪简单SQL Server恢复模型一样工作的情况。 您应该避免将恢复模型从完全更改为简单。 如果必须这样做，请立即进行完整的数据库备份，以为后续备份设置LSN链。

翻译自: https://www.sqlshack.com/pseudo-simple-sql-server-recovery-model-2/

sql server伪列