aws rds监控慢sql_AWS RDS SQL Server中的本机差异备份概述

最新推荐文章于 2024-02-17 19:23:27 发布

culuo4781

最新推荐文章于 2024-02-17 19:23:27 发布

阅读量297

点赞数

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

原文链接：https://www.sqlshack.com/an-overview-of-native-differential-backups-in-aws-rds-sql-server/

版权

aws rds监控慢sql

This article explores the differential backups in AWS RDS SQL Server using recovery scenarios.

本文探讨了使用恢复方案的AWS RDS SQL Server中的差异备份。

介绍 (Introduction)

Amazon RDS offers a platform as a service (PaaS) solution for highly available SQL instances. We do not maintain the infrastructure in RDS and pay only for the resource usage. It is an important aspect to know the way of data recovery in case of any unfortunate scenario. You can learn more about AWS RDS by checking the articles in AWS RDS category.

Amazon RDS为高可用性SQL实例提供了平台即服务（PaaS）解决方案。我们不维护RDS中的基础结构，而只为资源使用付费。在任何不幸的情况下，了解数据恢复的方式是一个重要方面。您可以通过查看AWS RDS类别中的文章来了解有关AWS RDS的更多信息。

In the previous article, we explored Recover Data in AWS RDS SQL Server using native full database backup and restore. Previously AWS RDS SQL Server did not support differential and log backups restore. Recently AWS published a blog announcement stating support of differential and log restoration.

在上一篇文章中，我们探索了使用本机完整数据库备份和还原在AWS RDS SQL Server中恢复数据。以前，AWS RDS SQL Server不支持差异备份和日志备份还原。最近，AWS发布了博客公告，声明了对差异和日志还原的支持。

In this article, we will explore the differential database backups in detail with examples.

在本文中，我们将通过示例详细探讨差异数据库备份。

先决条件 (Prerequisite)

AWS RDS SQL Server – Launching a new database instance for creating a new Amazon RDS SQL instance AWS RDS SQL Server –启动新的数据库实例以创建新的Amazon RDS SQL实例
You should have an AWS S3 bucket in the same region. You can follow the AWS RDS SQL Server migration using native backups article for configuring an S3 bucket
您应该在同一区域中有一个AWS S3存储桶。您可以按照《使用本机备份进行AWS RDS SQL Server迁移》文章中的说明配置S3存储桶

In this article, my SQL instance name is sqlshackdemo, as shown in the following image:

在本文中，我SQL实例名称为sqlshackdemo，如下图所示：

This instance public accessibility property should be true so that you can connect it using SSMS. Connect RDS instance using the endpoint:

此实例的公共可访问性属性应为true，以便您可以使用SSMS进行连接。使用端点连接RDS实例：

Create a new database and table with sample data using the following query:

使用以下查询使用示例数据创建一个新的数据库和表：

CREATE DATABASE Employees;
Create table EmpData
( EmpID int,
  EmpName varchar(30)
)
 
Insert into EmpData values (1, 'Rajendra')
Insert into EmpData values (2, 'Kusum')

在AWS S3存储桶中执行AWS RDS SQL Server的完整数据库备份 (Perform a full database backup of AWS RDS SQL Server in AWS S3 bucket )

As we know, a base for a differential backup is a full backup in SQL Server. It takes database backup of extents changed from the last full backup.

众所周知，差异备份的基础是SQL Server中的完整备份。它需要从上次完整备份更改的扩展区的数据库备份。

We need the following parameters in the command msdb.dbo.rds_backup_database:

我们在命令msdb.dbo.rds_backup_database中需要以下参数：

@Source_db_name: It is the database for which we want to take backup @Source_db_name ：这是我们要备份的数据库
@S3_arn_to_backup_to: Specify the Amazon S3 ARN, followed by a backup file name @ S3_arn_to_backup_to：指定Amazon S3 ARN，后跟备份文件名
@overwrite_s3_backup_file: If a backup file with a similar name exists, you can specify this parameter to overwrite an existing file. We can use a unique name if you do not want to overwrite the file @ overwrite_s3_backup_file：如果存在具有类似名称的备份文件，则可以指定此参数来覆盖现有文件。如果您不想覆盖文件，我们可以使用唯一的名称

EXEC msdb.dbo.rds_backup_database 
     @source_db_name = 'Employees', 
     @s3_arn_to_backup_to = 'arn:aws:s3:::rdstestsql/Employees_Full.bak', 
     @overwrite_S3_backup_file = 1;

For a new RDS instance, you might get following error message once you execute the command to take native DB backup:

对于新的RDS实例，执行命令执行本机数据库备份后，您可能会收到以下错误消息：

By default, the RDS SQL instance does not allow database backup and restore. Navigate to Options groups on the Amazon RDS homepage and select the option group that has SQLSERVER_BACKUP_RESTORE. If you do not have any options group, you can create an option group and add this parameter SQLSERVER_BACKUP_RESTORE:

默认情况下，RDS SQL实例不允许数据库备份和还原。导航到亚马逊RDS主页选项组 ，选择具有SQLSERVER_BACKUP_RESTORE选项组。如果没有任何选项组，则可以创建一个选项组并添加此参数SQLSERVER_BACKUP_RESTORE ：

Once we created an options group, modify the RDS instance properties. We can change the option group from default to the previously created options group (rdstestgroup in my case). We also need to apply changes immediately else it will modify RDS instance in the next scheduled maintenance window. You can see the scheduled maintenance window in the following screenshot:

创建选项组后，修改RDS实例属性。我们可以将选项组从默认更改为先前创建的选项组（在我的情况下为rdstestgroup）。我们还需要立即应用更改，否则它将在下一个计划的维护窗口中修改RDS实例。您可以在以下屏幕截图中看到计划的维护窗口：

It might take some time to apply changes. You can monitor the status of AWS RDS SQL Instance. Its status should be available. In the following screenshot, it shows the Modifying status that shows changes is in progress:

应用更改可能需要一些时间。您可以监视AWS RDS SQL实例的状态。它的状态应该可用。在以下屏幕截图中，它显示了正在显示更改的“正在修改”状态：

Once the instance is available again, execute the command specified above for taking a full database backup:

一旦实例再次可用，执行上面指定的命令以进行完整的数据库备份：

We can see that full database backup is in progress. Task id for the backup is 1. Initially, it shows the status CREATED:

我们可以看到完整的数据库备份正在进行中。备份的任务ID为1。最初，它显示状态CREATED ：

We can check the full backup status using the following RDS procedure msdb.dbo.rds_task_status supplying the task id in the parameter:

我们可以使用以下RDS过程msdb.dbo.rds_task_status在参数中提供任务ID来检查完整备份状态：

EXEC msdb..rds_task_status 
     @task_id = 1;

We can also browse the Amazon S3 bucket and verify the backup file, as shown below:

我们还可以浏览Amazon S3存储桶并验证备份文件，如下所示：

在AWS S3存储桶中执行数据库差异备份 (Perform a database differential backup in AWS S3 bucket)

Once a full backup is completed, let’s do some insert, update operations in the database. It will generate some activity in the differential database backup:

完整备份完成后，让我们在数据库中进行一些插入，更新操作。它将在差异数据库备份中生成一些活动：

INSERT INTO EmpData VALUES(3,  'Akshita');
INSERT INTO EmpData VALUES(4,  'Manoj');
Update EmpData set EmpName='John' where EmpID=4

We use the same stored procedure rds_backup_database for differential database backup. We can specify @type=’DIFFERENTIAL’ in the query for taking a differential backup:

我们使用相同的存储过程rds_backup_database进行差异数据库备份。我们可以在查询中指定@ type ='DIFFERENTIAL'进行差异备份：

EXEC msdb.dbo.rds_backup_database 
     @source_db_name = 'Employees', 
     @s3_arn_to_backup_to = 'arn:aws:s3:::rdstestsql/Employees_diff.bak', 
     @overwrite_S3_backup_file = 1, 
     @type = 'DIFFERENTIAL';

In the backup status command, we can see task_type BACKUP_DB_DIFFERENTIAL that shows it is executing a differential database backup for the AWS RDS SQL database:

在备份状态命令中，我们可以看到task_type BACKUP_DB_DIFFERENTIAL ，显示它正在为AWS RDS SQL数据库执行差异数据库备份：

Perform a database differential backup in AWS S3 bucket

Once the differential backup is completed (lifecycle=SUCCESS), let’s use the following query and intentionally perform some changes in the table:

差异备份完成后（lifecycle = SUCCESS），让我们使用以下查询并有意对表进行一些更改：

It updates an existing record in the EmpData table and removes a row for EmpID 4:

它更新EmpData表中的现有记录，并删除EmpID 4的行：

Update EmpData set EmpName='XYZ' where EmpID=3
Delete from EmpData where EmpID=4

从存储在AWS S3存储桶中的完整备份执行AWS RDS SQL Server数据库还原 (Perform an AWS RDS SQL Server database restoration from a full backup stored in AWS S3 bucket)

We want to recover data in this EmpData table, and it will require the restoration of a full and differential database backup on a separate database. Later, we can export the data from a restored database to the original database.

我们要恢复此EmpData表中的数据，这将需要在单独的数据库上恢复完整和差异数据库备份。以后，我们可以将数据从还原的数据库导出到原始数据库。

Note: We will create a new database from the full and differential database backup for demo in this article.

注意：本文将通过完整和差异数据库备份创建一个新数据库，以进行演示。

We use the msdb.dbo.rds_restore_database command for database restoration. It requires the following parameters:

我们使用msdb.dbo.rds_restore_database命令进行数据库还原。它需要以下参数：

@Restore_db_name: It is the restored database name @Restore_db_name：这是还原的数据库名称
@s3_arn_to_restore_from: It is the full path of the database backup. You can get the ARN from the Amazon S3 bucket @ s3_arn_to_restore_from：这是数据库备份的完整路径。您可以从Amazon S3存储桶中获取ARN
@with_norecovery: We want to restore further differential database backup after the full backup restoration. It requires full backup restoration in the NORECOVERY mode. In AWS RDS SQL Server, we need to use parameter@with_norecovery：我们要在完全备份还原后还原进一步的差异数据库备份。它需要以NORECOVERY模式进行完全备份还原。在AWS RDS SQL Server中，我们需要使用参数 @with_norecovery=1 @ with_norecovery = 1
@Type: We specified the backup file type in the @Type：我们在@s3_arn_to_restore_from parameter. For a full backup restoration, use the @ s3_arn_to_restore_from参数中指定了备份文件类型。要进行完整的备份还原，请使用@type=Full parameter @ type = Full参数

We cannot overwrite or replace a database in the AWS RDS SQL Server instance. If you try to restore an existing database, it gives the following error message:

我们无法覆盖或替换AWS RDS SQL Server实例中的数据库。如果尝试还原现有数据库，则会显示以下错误消息：

Execute the query for database backup restoration and check the task status. It shows the task_type value as RESTORE_DB_NORECOVERY for the database restoration in NORECOVERY mode:

执行查询以恢复数据库备份并检查任务状态。它将task_type值显示为RESTORE_DB_NORECOVERY 以NORECOVERY模式恢复数据库：

EXEC msdb..rds_task_status 
     @task_id = 6

Once the database restoration is completed, we can refresh the database in Object Explorer of SSMS. It shows the database in the restoring mode. We cannot access the database in the restoring mode, but SQL Server allows you to restore subsequence backups:

数据库还原完成后，我们可以在SSMS的对象资源管理器中刷新数据库。它显示了处于还原模式的数据库。我们无法在还原模式下访问数据库，但是SQL Server允许您还原子序列备份：

从存储在AWS S3存储桶中的差异备份执行AWS RDS SQL Server数据库还原 (Perform an AWS RDS SQL Server database restoration from a differential backup stored in AWS S3 bucket)

Now, let’s restore the differential backup on the Employee_restore database. We need to use the following parameters in this command:

现在，让我们在Employee_restore数据库上还原差异备份。我们需要在此命令中使用以下参数：

@Restore_db_name: It should be the same database restored earlier from the full backup in NORECOVERY mode @Restore_db_name：应该与以前以NORECOVERY模式从完整备份还原的数据库相同
@s3_arn_to_restore_from: It is the full path of the differential database backup. @ s3_arn_to_restore_from：这是差异数据库备份的完整路径。 @with_norecovery: We want to restore a differential database backup after the full backup restoration and recover the database to access it. Use the parameter @with_norecovery：我们要在完全备份还原后还原差异数据库备份，并恢复数据库以对其进行访问。使用参数@with_norecovery=0 for database recovery @ with_norecovery = 0进行数据库恢复
@Type: We specified the backup file type in the @Type：我们在@s3_arn_to_restore_from parameter. For a differential backup restoration, use the @ s3_arn_to_restore_from参数中指定了备份文件类型。对于差异备份还原，请使用@type=DIFFERENTIAL parameter @ type = DIFFERENTIAL参数

EXEC msdb.dbo.rds_restore_database 
     @restore_db_name = 'Employees_restore', 
     @s3_arn_to_restore_from = 'arn:aws:s3:::rdstestsql/Employees_diff.bak', 
     @with_norecovery = 0, 
     @type = 'DIFFERENTIAL';

It restores the differential database backup and recovers the database:

它还原差异数据库备份并还原数据库：

database recovery after a differential backup restoration

Refresh the databases in Object Explorer, and it is online:

在对象资源管理器中刷新数据库，该数据库处于联机状态：

Let’s compare the records in the original database and the restored database. We can see that records exist in the restored database. We have restored the database to a state that exists at differential backup completion:

让我们比较原始数据库和还原的数据库中的记录。我们可以看到还原的数据库中存在记录。我们已将数据库还原到差异备份完成时存在的状态：

Note: As per official AWS RDS SQL Server, it supports for transaction log backup with point-in-time recovery as well. We restore transaction log backup using the msdb.dbo.rds_restore_log stored procedure. However, it does not show the procedures for taking a transaction log backup for the RDS SQL database. I will be in touch with the Amazon support center on this and cover point-in-time recovery in subsequence article. You can go through official documentation Importing and Exporting SQL Server Databases.

注意：根据官方的AWS RDS SQL Server，它还支持带有时间点恢复的事务日志备份。我们使用msdb.dbo.rds_restore_log存储过程还原事务日志备份。但是，它没有显示为RDS SQL数据库进行事务日志备份的过程。我将就此与Amazon支持中心联系，并在后续文章中介绍时间点恢复。您可以查看官方文档“ 导入和导出SQL Server数据库” 。

结论 (Conclusion)

In this article, we explored database recovery in the AWS RDS SQL Server database using full and differential database backups. You should explore these databases and explore it in your environment.

在本文中，我们探讨了使用完整和差异数据库备份在AWS RDS SQL Server数据库中进行数据库恢复。您应该浏览这些数据库并在您的环境中对其进行浏览。