如何在SQL Server拉伸数据库上执行备份和还原操作

最新推荐文章于 2022-11-16 13:47:02 发布

culuo4781

最新推荐文章于 2022-11-16 13:47:02 发布

阅读量327

点赞数

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

原文链接：https://www.sqlshack.com/perform-backup-restore-operations-sql-server-stretch-databases/

版权

The Stretch database is one of the latest and most-exciting features that has been released with SQL Server 2016. In the previous article, we examined the concept behind it, the steps to implement it, monitor it and how it can be disabled. As a natural follow-up, we will explore the backup and restore topic when it comes to those kind of databases.

Stretch数据库是SQL Server 2016发行的最新，最令人兴奋的功能之一。在上一篇文章中，我们检查了它的概念，实现它的步骤，监视它以及如何禁用它。作为自然的后续活动，我们将探讨有关此类数据库的备份和还原主题。

理论 (The theory)

We are all aware that backups are an integral part of database administrators’ daily tasks. Rather than trying to persuade you how important is to have a proper backup strategy, we will directly jump to the topic.

我们都知道备份是数据库管理员日常工作的组成部分。与其说服您采用适当的备份策略有多重要，我们不如直接跳到该主题。

Backups that have been created against a stretched database hold only the local data and also have a flag for the rows in the table that are still to be moved to the cloud as of the time of the backup creation. They do not keep the data that is already in Azure so the question then is: Do we need to take backups of a stretched database?

针对扩展数据库创建的备份仅保存本地数据，并且还为表中的行创建了一个标志，这些行在创建备份时仍要移到云中。他们不保留Azure中已经存在的数据，因此问题是：我们是否需要对扩展数据库进行备份？

是! (Yes!)

If you are leveraging on this feature to migrate your “cold” data to the cloud, you have one or more tables that are continuously sending data to Azure and you do not store any rows locally for them (once the initial sync finishes), but in majority of the cases you would still be having some local-only tables. Regular backups are still essential in order to be sure that the data in the local tables would be protected. In theory, it is possible to move the data from all of the tables to the cloud (probably this would be a rare scenario), but even then you could benefit of taking backups in order to preserve other objects in the database like users, stored procedures etc.

如果您正在利用此功能将“冷”数据迁移到云，则您有一个或多个表不断向Azure发送数据，并且您没有在本地为它们存储任何行（一旦完成初始同步），但是在大多数情况下，您仍然会有一些仅本地表。常规备份对于确保本地表中的数据将受到保护仍然至关重要。从理论上讲，可以将所有表中的数据移动到云中（可能是很少见的情况），但是即使那样，您也可以受益于进行备份，以将数据库中的其他对象（如用户）存储在存储中程序等

With regards to the data in Azure, it is protected in several ways:

关于Azure中的数据，它以多种方式受到保护：

Automatic Snapshots at least every 8 hours
至少每8小时自动快照
Geo-redundancy
地理冗余
Creation of a Snapshot when Azure SQL Database is being dropped
删除Azure SQL数据库时创建快照

Basically, we can be confident that the rows already sitting in the cloud are protected and our thoughts should be directed towards a proper backup strategy for the local data (as if we were not sending any data to Azure).

基本上，我们可以确信已经存在于云中的行受到了保护，我们的思想应该针对本地数据的正确备份策略（就像我们没有向Azure发送任何数据一样）。

Having in mind the theory, we will now focus on the practical side and test several different scenarios, so the fun part begins here 🙂

牢记理论，我们现在将重点放在实践方面并测试几种不同的场景，因此有趣的部分从这里开始🙂

备份差异 (Backup difference)

First, let’s examine the backups in terms of sizes before and after we transfer our data to Azure. We are using again the “StackOverflow” database and one of its largest tables “dbo.Votes”:

首先，让我们在将数据传输到Azure之前和之后检查备份大小。我们再次使用“ StackOverflow”数据库及其最大的表之一“ dbo.Votes”：

For the time being, all of the records from this table are sitting locally on our SQL Server:

目前，该表中的所有记录都位于SQL Server的本地位置：

 
USE  [StackOverflow]
GO  
SELECT GETDATE() as [current_date]
EXEC sp_spaceused N'dbo.Votes'
EXEC sp_spaceused N'dbo.Votes', @mode = 'REMOTE_ONLY'  
EXEC sp_spaceused N'dbo.Votes', @mode = 'LOCAL_ONLY'

Create a FULL backup of the database and enable data movement for table “dbo.Votes”:

创建数据库的完整备份，并为表“ dbo.Votes”启用数据移动：

Note: we are not going in details through the whole process as it is described in the previous article.

注意：我们不会像上一篇文章中所描述的那样详细介绍整个过程。

The migration of 67 million rows to the cloud will take some time, so be patient and have several coffees/beers 🙂 . While you are waiting, create an intermittent FULL backup in the middle of the data movement process (it will be used at a later stage).

将6700万行迁移到云需要花费一些时间，因此请耐心等待，并准备几杯咖啡/啤酒🙂。等待期间，在数据移动过程的中间创建一个间歇性的FULL备份（将在以后使用）。

After several hours, the process is over:

几个小时后，该过程结束：

 
USE  [StackOverflow]
GO  
SELECT GETDATE() as [current_date]
EXEC sp_spaceused N'dbo.Votes'
EXEC sp_spaceused N'dbo.Votes', @mode = 'REMOTE_ONLY'  
EXEC sp_spaceused N'dbo.Votes', @mode = 'LOCAL_ONLY'

Create another FULL backup and compare the one taken prior to the migration with the most recent one:

创建另一个完整备份，并将迁移之前进行的备份与最新备份进行比较：

 
SELECT  
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupmediafamily.physical_device_name
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)  AND msdb..backupset.type ='D'
ORDER BY  
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_finish_date

There is a difference between the backups which is approximately with the size of the table (backup_size column is in bytes) we already moved to Azure. Once our tables have been moved to the cloud, they do not occupy space on-premise and respectively, in the backups.

备份之间的差异大约与我们已经移至Azure的表的大小（backup_size列以字节为单位）有关。将我们的表移到云中后，它们将不会在内部和备份中分别占用空间。

还原删除SQL Azure数据库 (Restore a dropped SQL Azure database)

Our “cold” data has already been sitting in Azure for quite some time, we can access it whenever it is required and it is not occupying additional space locally. That is perfect, but what will happen if the SQL Azure database is not available due to any reason or completely dropped? As discussed in the first section of this article, automatic snapshots are being created on a regular basis for the data in the cloud and additionally, one more snapshot prior to its deletion. For the sake of the demo, we will deliberately drop the SQL Azure database and try to restore it using this last, supplementary snapshot.

我们的“冷”数据已经存在于Azure中很长时间了，我们可以在需要时访问它，并且它不会在本地占用额外的空间。那是完美的，但是如果由于任何原因SQL Azure数据库不可用或被完全删除会发生什么？如本文第一节所述，将定期为云中的数据创建自动快照，此外，还会在删除快照之前创建一个快照。为了演示起见，我们将故意删除SQL Azure数据库，并尝试使用最后一个补充快照来还原它。

Open the Azure portal, select the database that is being used for the migration of the rows and delete it:

打开Azure门户，选择用于行迁移的数据库并将其删除：

The database has been completely dropped and we have “lost” the data that was already residing in Azure:

数据库已完全删除，我们“丢失”了已经存在于Azure中的数据：

 
USE  [StackOverflow]
GO  
SELECT GETDATE() as [current_date]
EXEC sp_spaceused N'dbo.Votes'
EXEC sp_spaceused N'dbo.Votes', @mode = 'REMOTE_ONLY'  
EXEC sp_spaceused N'dbo.Votes', @mode = 'LOCAL_ONLY'

The data has been “lost”, but not completely as we have this extra snapshot and relatively easy, straightforward process to restore it. From the Azure portal, open the respective server that was hosting your database, go to Deleted Databases, select the one you want to recover and hit OK:

数据已“丢失”，但并非完全丢失，因为我们拥有此额外快照和相对简单，直接的还原过程。在Azure门户中，打开托管数据库的相应服务器，转到“删除的数据库”，选择要恢复的服务器，然后单击“确定”：

The operation might take several minutes depending on the size of the data and how fast your network is. After the restoration completes, you should be able to see the database again:

该操作可能需要几分钟，具体取决于数据的大小和网络的速度。恢复完成后，您应该可以再次看到数据库：

Is that all? Are we ready to access our table again? The database is there, but the status of the Stretch Database is not healthy and we are still unable to query the rows in Azure:

这就是全部？我们准备好再次访问我们的表了吗？该数据库在那里，但是Stretch Database的状态不正常，我们仍然无法查询Azure中的行：

There is one more step missing – after the restore, we have to reauthorize the connection between the on-premise database and the remote Azure one. This has been implemented as a security measure which prevents someone from taking the backup of your database, restore it on another instance and access both your local and cloud data directly. In order to resume the connection, we need the credentials that have been used prior to the restore:

还缺少一个步骤–还原后，我们必须重新授权本地数据库和远程Azure数据库之间的连接。这已作为一项安全措施实施，可以防止某人进行数据库备份，在另一个实例上还原数据库以及直接访问本地和云数据。为了恢复连接，我们需要在还原之前使用的凭据：

 
USE  [StackOverflow]
GO  
select * from sys.database_scoped_credentials

As soon as we have them, we can now do the actual reauthorization:

有了它们后，我们现在就可以进行实际的重新授权：

 
USE  [StackOverflow]
GO  
EXEC sp_rda_deauthorize_db
USE  [StackOverflow]
GO  
EXEC sp_rda_reauthorize_db
    @credential = N'sqlstretchserver.database.windows.net',
    @with_copy = 0 ;  -- mandatory parameter - 0 if you will use the old db and 1 if you will use a new db and copy the rows to it
GO

Yes, but we are not quite there yet. We receive an error which might be very misleading unless we carefully read it: “Cannot open database “RDAStackOverflow653EB5BC-3FBE-42E8-BCAD-CF920321CEF6” requested by the login”. Comparing this name with the recently restored database is the key in our situation as they are different:

是的，但我们还没有到那儿。除非仔细阅读，否则我们会收到一个可能会引起误解的错误：“无法打开登录名要求的数据库“ RDAStackOverflow653EB5BC-3FBE-42E8-BCAD-CF920321CEF6”。在我们的情况下，将此名称与最近恢复的数据库进行比较是关键，因为它们不同：

By default, Azure is putting a suffix representing the date of the snapshot used for the restore operation. One additional step is required before the reauthorization which is to rename it:

默认情况下，Azure放置一个后缀，代表用于还原操作的快照的日期。在重新授权之前，还需要执行另一步骤，即对其进行重命名：

 
ALTER DATABASE [RDAStackOverflow653EB5BC-3FBE-42E8-BCAD-CF920321CEF6_2016-08-28T08 -42Z] MODIFY NAME = [RDAStackOverflow653EB5BC-3FBE-42E8-BCAD-CF920321CEF6]

Now, retry to reestablish the connection:

现在，尝试重新建立连接：

 
USE  [StackOverflow]
GO  
EXEC sp_rda_deauthorize_db
USE  [StackOverflow]
GO  
EXEC sp_rda_reauthorize_db
    @credential = N'sqlstretchserver.database.windows.net',
    @with_copy = 0 ;  -- mandatory parameter - 0 if you will use the old db and 1 if you will use a new db and copy the rows to it
GO

The query succeeds and our Azure data is live again:

查询成功，并且我们的Azure数据再次处于活动状态：

With several simple steps our data is back and accessible again Note that the procedure for restoring a live SQL Azure database is identical and you can basically follow the same drill.

通过几个简单的步骤，我们的数据即可恢复并可以再次访问。请注意，还原实时SQL Azure数据库的过程是相同的，并且您基本上可以遵循相同的练习。

在Azure迁移过程中还原本地数据库 (Restore an on-premise database in the middle of Azure migration)

The last use case that we are going to explore in this article is what will happen if we restore a database as of date and time when the migration to cloud has not finished yet and we have rows sitting locally and rows in Azure. We have already enabled the data movement for table “dbo.Votes” and the data is being sent to the cloud:

我们将在本文中探讨的最后一个用例是，如果我们还原到日期和时间的数据库恢复到云的迁移尚未完成，并且在本地有行并且在Azure中有行，则会发生什么情况。我们已经为表“ dbo.Votes”启用了数据移动，并且数据正在发送到云中：

 
USE  [StackOverflow]
GO  
SELECT GETDATE() as [current_date]
EXEC sp_spaceused N'dbo.Votes'
EXEC sp_spaceused N'dbo.Votes', @mode = 'REMOTE_ONLY'  
EXEC sp_spaceused N'dbo.Votes', @mode = 'LOCAL_ONLY'

As of this moment, the table has rows locally and in Azure (67 million of rows still to be migrated). Use the on-demand, intermittent backup that was created while the migration was taking place and restore it:

到目前为止，该表在本地和Azure中都有行（仍有6700万行要迁移）。使用在进行迁移时创建的按需间歇备份并还原它：

Check what happened with the data:

检查数据发生了什么：

 
USE  [StackOverflow]
GO  
SELECT GETDATE() as [current_date]
EXEC sp_spaceused N'dbo.Votes'
EXEC sp_spaceused N'dbo.Votes', @mode = 'REMOTE_ONLY'  
EXEC sp_spaceused N'dbo.Votes', @mode = 'LOCAL_ONLY'

The rows in Azure are still not available as we need to perform the reauthorization after the restore:

Azure中的行仍然不可用，因为我们需要在还原后执行重新授权：

 
USE  [StackOverflow]
GO  
EXEC sp_rda_deauthorize_db
USE  [StackOverflow]
GO  
EXEC sp_rda_reauthorize_db
    @credential = N'sqlstretchserver.database.windows.net',
    @with_copy = 0 ;  -- mandatory parameter - 0 if you will use the old db and 1 if you will use a new db and copy the rows to it
GO

Once the connection has been reestablished, the data in the cloud is now accessible and the migration picks up where it left off.

重新建立连接后，现在就可以访问云中的数据，并且迁移从中断的地方开始。

There are some tricky moments when it comes to the restoration of an on-premise SQL database that is marked as a Stretched one and SQL Azure database part of such configuration. I have tried to cover them in this article and hope it will save you time and efforts next time you face the need to recover either one of them.

在还原本地SQL数据库时，有些棘手的时刻被标记为这种配置的扩展数据库和SQL Azure数据库的一部分。我已尝试在本文中介绍它们，并希望当您下次需要恢复其中任何一个时，它将节省您的时间和精力。

Thanks for reading!

谢谢阅读！

有用的资料 ( Useful sources )

翻译自: https://www.sqlshack.com/perform-backup-restore-operations-sql-server-stretch-databases/

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
如何在SQL Server拉伸数据库上执行备份和还原操作

The Stretch database is one of the latest and most-exciting features that has been released with SQL Server 2016. In the previous article, we examined the concept behind it, the steps to implement...
复制链接

扫一扫