部分SQL Server数据库备份

最新推荐文章于 2023-07-17 16:32:35 发布

culuo4781

最新推荐文章于 2023-07-17 16:32:35 发布

阅读量482

点赞数

文章标签：数据库 python linux java 大数据

原文链接：https://www.sqlshack.com/a-partial-backup-of-sql-databases/

版权

This article explores the partial backup SQL Database strategy in SQL Server and its usages for read-only filegroups.

本文探讨了SQL Server中的部分备份SQL数据库策略及其在只读文件组中的用法。

介绍 (Introduction)

In the article, Piecemeal Database Restores in SQL Server, we explored the benefit of database filegroups and their usages to minimize application downtime. We can restore the critical data filegroups for application availability and later restore the filegroup with less critical data. It saves overall database recovery time and increases database availability.

在“ SQL Server中的碎片数据库还原”一文中，我们探讨了数据库文件组的好处及其用法，以最大程度地减少应用程序停机时间。我们可以还原关键数据文件组以提高应用程序的可用性，然后再使用不太重要的数据还原文件组。它节省了总体数据库恢复时间，并提高了数据库可用性。

In the following figure, we can reference a database with multiple filegroups and backup sequence:

在下图中，我们可以引用具有多个文件组和备份序列的数据库：

Suppose we have a database with four filegroups FG-Primary, FG-A, FG-B, and FG-C. FG-C filegroup is marked as read-only. It contains archived data and it cannot be modified. Users access data from this filegroup tables for read-only or reporting purposes:

假设我们有一个包含四个文件组FG-Primary，FG-A，FG-B和FG-C的数据库。 FG-C文件组被标记为只读。它包含存档的数据，无法修改。用户出于只读或报告目的而访问此文件组表中的数据：

In full database backup, we take complete backup including all filegroups. We might not require backup for read-only filegroup because its data is static. If we find a way to exclude this filegroup in backup, it can save space as well as time. We can perform database recovery as well in less time. SQL Server provides partial backups to accomplish this task. Let’s explore the partial backup in the next section.

在完整数据库备份中，我们将进行完整备份，包括所有文件组。我们可能不需要备份只读文件组，因为它的数据是静态的。如果我们找到一种在备份中排除此文件组的方法，则可以节省空间和时间。我们也可以在更少的时间内执行数据库恢复。 SQL Server提供了部分备份来完成此任务。让我们在下一部分中探索部分备份。

部分备份SQL数据库概述 (Overview of Partial backup SQL Database)

A partial backup allows taking backup for read-write filegroups data for the SQL Server database. It does not include read-only filegroup data in the partial backups. It takes backup for primary and read-write filegroups and excludes read-only filegroup data:

部分备份允许备份SQL Server数据库的读写文件组数据。它在部分备份中不包括只读文件组数据。它需要备份主文件组和读写文件组，并且不包括只读文件组数据：

Partial backup is available from SQL Server 2005 onwards
从SQL Server 2005起可以使用部分备份
It is supported in all database recovery models, i.e. simple, full and bulk-logged
所有数据库恢复模型均支持该功能，即简单，完整和批量记录

Suppose we have an online shopping database. This database holds customer purchase records.

假设我们有一个在线购物数据库。该数据库保存客户购买记录。

This database contains the following filegroups:

该数据库包含以下文件组：

FG-Primary: It holds master tables
FG-Primary：它包含主表
FG-A: It holds customer records and service request information
FG-A：它保存客户记录和服务请求信息
FG-B: It holds customer orders for the current financial year
FG-B：它持有当前财政年度的客户订单
FG-C: It holds customer previous years’ data. This data is static and does not change. We require this data to generate a report for internal usage
FG-C：它保存客户过去几年的数据。此数据是静态的，不会更改。我们需要这些数据来生成内部使用情况报告

Use the following scripts for creating the database to meet the above request.

使用以下脚本创建数据库来满足上述要求。

Script to create partialFG database with multiple filegroups:

用于创建具有多个文件组的partialFG数据库的脚本：

CREATE DATABASE [PartialFG] ON PRIMARY
(
                                       NAME = N'PartialFG', 
                                       FILENAME = N'C:\Export\PartialFG.mdf'
), FILEGROUP [FG-A]
(
                                       NAME = N'PartialFG_A', 
                                       FILENAME = N'C:\Export\PartialFG_A.ndf'
), FILEGROUP [FG-B]
(
                                       NAME = N'PartialFG_B', 
                                       FILENAME = N'C:\Export\PartialFG_B.ndf'
), FILEGROUP [FG-C]
(
                                       NAME = N'PartialFG_C', 
                                       FILENAME = N'C:\Export\PartialFG_C.ndf'
) log ON
(
                                       NAME = N'PartialFG_log', 
                                       FILENAME = N'C:\Export\PartialFG_log.ldf'
);

Script to change recovery model of PartialFG database to Simple:

将PartialFG数据库的恢复模型更改为简单的脚本：

ALTER DATABASE PartialFG SET RECOVERY SIMPLE
GO

Create a table in filegroup PartialFG_A:

在PartialFG_A文件组中创建一个表：

CREATE TABLE dbo.Customers
(ID           INT NOT NULL, 
 CustomerName NVARCHAR(50) NOT NULL,
)
ON [FG-A];
INSERT INTO Customers
VALUES
(1, 
 'Raj'
);
GO

Create a table in filegroup PartialFG_B:

在文件组PartialFG_B中创建一个表：

CREATE TABLE dbo.Product
(ID          INT NOT NULL, 
 productname NVARCHAR(50) NOT NULL,
)
ON [FG-B];
INSERT INTO product
VALUES
(1, 
  'DB'
);
GO

Create a table in filegroup PartialFG_B:

在文件组PartialFG_B中创建一个表：

CREATE TABLE dbo.archive
(ID          INT NOT NULL, 
 archivedata NVARCHAR(50) NOT NULL,
)
ON [FG-C];
INSERT INTO archive
VALUES
(1, 
 'archivedata'
);

Modify filegroup FG-C from read-write to read-only:

将文件组FG-C从读写修改为只读：

ALTER DATABASE PartialFG MODIFY FILEGROUP [FG-C] READONLY
GO

We might get the following error message if the database is in use by other sessions. You should close the sessions and execute an alter database command again:

如果其他会话正在使用该数据库，我们可能会收到以下错误消息。您应该关闭会话并再次执行alter database命令：

You get the following confirmation message once the specific filegroup is marked as read-only:

将特定文件组标记为只读后，您会收到以下确认消息：

You cannot insert, update any data in the read-only filegroup. It gives the following error message for any DML operation:

您不能在只读文件组中插入或更新任何数据。对于任何DML操作，它都会给出以下错误消息：

Error while inserting data in read-only filegroup

SELECT name, 
      physical_name, 
      state_desc, 
      is_read_only
FROM sys.database_files;

In the screenshot, we can see that is_read_only flag is set to 1 for data file PartialFG_C that belongs to FG_C filegroup:

在屏幕截图中，我们可以看到属于FG_C文件组的数据文件PartialFG_C的is_read_only标志设置为1：

Let’s take a full database backup SQL Database. It includes backup for filegroups including read-only filegroup as well. It acts as a restoration point for further database backups. Since this backup will include read-only filegroup data as well, we can restore this backup file on a secured location to take reference once required:

让我们进行完整的数据库备份SQL数据库。它包括文件组的备份，也包括只读文件组。它充当进一步数据库备份的还原点。由于此备份还将包括只读文件组数据，因此我们可以在安全的位置还原此备份文件，以在需要时进行参考：

backup database PartialFG to disk='T:\DB\PartialFG.bak' with stats=10

In the output, we can verify that it includes read-only filegroup data as well:

在输出中，我们可以验证它是否还包含只读文件组数据：

Generate some workload before taking the partial database backup:

在进行部分数据库备份之前生成一些工作负载：

INSERT INTO Customers
VALUES
(1, 
 'Raj'
);
UPDATE product
  SET 
      productname = 'database';

Taking a partial database backup SQL Database is straightforward. We require to add READ_WRITE_FILEGROUPS argument in the backup database command. This option instructs SQL Server to initiate a partial filegroup backup for read-write filegroups.

进行部分数据库备份SQL数据库非常简单。我们需要在备份数据库命令中添加READ_WRITE_FILEGROUPS参数。此选项指示SQL Server为读写文件组启动部分文件组备份。

In the output, we can see it does not include backup for read-only filegroup FG-C. It meets our requirement to exclude read-only filegroup in the database backup:

在输出中，我们可以看到它不包括只读文件组FG-C的备份。满足我们在数据库备份中排除只读文件组的要求：

Generate database activity for taking differential database backup:

生成数据库活动以进行差异数据库备份：

INSERT INTO Customers
VALUES
(1, 
 'Monu'
);
UPDATE product
  SET 
      productname = 'database SQL';

To take a differential backup, we need to add WITH DIFFRENTIAL clause in the script:

要进行差异备份，我们需要在脚本中添加WITH DIFFRENTIAL子句：

BACKUP DATABASE PartialFG READ_WRITE_FILEGROUPS
TO DISK = N'T:\DB\PartialFG_readwrite_diff.bak'
WITH DIFFERENTIAL
GO

You might think of a question here. Differential backup SQL Database always takes full backup as a reference point. It includes data changes from the last full backup. In this case, we took partial differential backup after a partial database backup.

您可能在这里想到一个问题。差异备份SQL数据库始终以完整备份为参考点。它包括自上次完整备份以来的数据更改。在这种情况下，我们在部分数据库备份之后进行了部分差异备份。

What will be the reference point of this partial differential backup?

此部分差异备份的参考点是什么？

We use the RESTORE HEADERONLY command to check the backup LSN from existing DB backup. The following queries check the LSN information for full, partial and partial differential database backup:

我们使用RESTORE HEADERONLY命令从现有数据库备份中检查备份LSN。以下查询检查LSN信息以进行完整，部分和部分差异数据库备份：

RESTORE HEADERONLY FROM DISK = 'C:\Export\PartialFG.bak';
RESTORE HEADERONLY FROM DISK = 'C:\Export\PartialFG_readwrite.bak';
RESTORE HEADERONLY FROM DISK = 'C:\Export\PartialFG_readwrite_diff.bak';

Look at the highlighted LSN information of all database backups:

查看所有数据库备份的突出显示的LSN信息：

Full database backup Last LSN = Partial database backup (DatabaseBackups LSN)
完整数据库备份上一个LSN =部分数据库备份（DatabaseBackups LSN）
Full database backup Last LSN Partial database differential backup (DatabaseBackups LSN)
完整数据库备份最近的LSN部分数据库差异备份（DatabaseBackups LSN）

It shows differential database backup points to partial database backup LSN. We require restoring partial backup before restoration of partial differential backup:

它显示了到部分数据库备份LSN的差异数据库备份点。我们要求先还原部分备份，然后再还原部分差异备份：

执行部分数据库还原 (Performing Partial Database restores )

In this section, we look at the database restoration process for the partial database backup SQL Database. We will explore different restore process for the partial database backups.

在本节中，我们将研究部分数据库备份SQL数据库的数据库还原过程。我们将针对部分数据库备份探索不同的还原过程。

恢复完整的部分备份 (Restoring a full Partial backup)

Here, we aim to perform the following backup’s restoration:

在此，我们旨在执行以下备份的还原：

Restore Full database backup in NORECOVERY mode: It restores a database from both read-write and read-only filegroups. Norecovery mode allows restoring a further database backup 还原在NORECOVERY模式下进行完整的数据库备份：它从读写文件组和只读文件组中还原数据库。 Norecovery模式允许还原进一步的数据库备份
Restore partial database backup in RECOVERY mode: In this step, SQL Server restores partial database backups and perform database recovery 在RECOVERY模式下还原部分数据库备份：在此步骤中，SQL Server还原部分数据库备份并执行数据库恢复

USE [master]
GO
     
RESTORE DATABASE [Restore-PartialFG]
FROM DISK =
    N'C:\Export\PartialFG.bak'
WITH NORECOVERY
GO
     
RESTORE DATABASE [Restore-PartialFG]
FROM DISK =
    N'C:\Export\PartialFG_readwrite.bak'
WITH RECOVERY
GO

After the restoration of full and partial database backup, all the filegroups are online. We can access the database from the read-only filegroup as well:

恢复完整和部分数据库备份后，所有文件组均处于联机状态。我们也可以从只读文件组访问数据库：

还原差异部分备份SQL数据库 (Restoring a differential Partial backup SQL Database)

In this method, we perform the following database restoration:

在这种方法中，我们执行以下数据库还原：

Restore full backup in NORECOVERY mode
以NORECOVERY模式还原完整备份
Restore partial database backup in NORECOVERY mode
以NORECOVERY模式还原部分数据库备份
Restore partial differential backup in RECOVERY mode
在恢复模式下还原部分差异备份

As highlighted earlier, we cannot apply partial differential backup directly on the partial differential backup. A partial differential backup contains data after the partial database backup. If we try to skip step 2 (Restore partial database backup), we get the following error message:

如前所述，我们不能将部分差异备份直接应用于部分差异备份。部分差异备份包含部分数据库备份之后的数据。如果我们尝试跳过步骤2（还原部分数据库备份），则会收到以下错误消息：

The message clearly states that the current state of the filegroup should have differential backup applied. We skipped the partial database backup; therefore, further partial differential backup gives the error

该消息明确指出，文件组的当前状态应应用差异备份。我们跳过了部分数据库备份；因此，进一步的部分差异备份会导致错误

Let’s follow the restore sequence and it works fine:

让我们按照还原顺序进行，它可以正常工作：

USE [master]
GO
         
RESTORE DATABASE [Restore-PartialFG]
FROM DISK =
    N'C:\Export\PartialFG.bak'
WITH NORECOVERY
GO
RESTORE DATABASE [Restore-PartialFG]
FROM DISK =
    N'C:\Export\PartialFG_readwrite.bak'
WITH NORECOVERY
GO
         
RESTORE DATABASE [Restore-PartialFG]
FROM DISK =
    N'C:\Export\PartialFG_readwrite_diff.bak'
WITH RECOVERY
GO

有关部分备份SQL数据库的有用要点 (Useful points about partial backup SQL Database)

Partial database backup is available in all recovery models
所有恢复模型均提供部分数据库备份
It is mostly useful for simple recovery model with read-only filegroups
它对于带有只读文件组的简单恢复模型最有用
We can exclude read-only filegroups in the partial database backups
我们可以在部分数据库备份中排除只读文件组
You cannot take partial database backup using SSMS graphical tool. We require t-SQL for taking partial database backup
您不能使用SSMS图形工具进行部分数据库备份。我们要求使用t-SQL进行部分数据库备份

结论 (Conclusion)

In this article, we explored partial backup SQL Databases for excluding read-only filegroups. It gives flexibility for the large database backup. Database backups are vital for recovery in any disaster scenario. You should plan, validate the restoration plan as per the criticality, data requirements RPO and RTO.

在本文中，我们探索了部分备份SQL数据库，以排除只读文件组。它为大型数据库备份提供了灵活性。数据库备份对于任何灾难情况下的恢复都是至关重要的。您应该根据重要性，数据要求RPO和RTO计划和验证恢复计划。

翻译自: https://www.sqlshack.com/a-partial-backup-of-sql-databases/

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
部分SQL Server数据库备份

This article explores the partial backup SQL Database strategy in SQL Server and its usages for read-only filegroups. 本文探讨了SQL Server中的部分备份SQL数据库策略及其在只读文件组中的用法。介绍 (Introduction) In the ar...
复制链接

扫一扫