filestream
The SQL Server FILESTREAM feature is available from SQL Server 2008 onwards. This feature allows the large BLOB objects to store into the file system and keeps metadata in the database tables. Before you go further in this article, let us have a quick overview of the FILESTREAM series articles.
从SQL Server 2008起可以使用SQL Server FILESTREAM功能。 此功能允许大型BLOB对象存储到文件系统中，并将元数据保留在数据库表中。 在继续本文之前，让我们快速了解FILESTREAM系列文章。
回顾性 (Retrospective)
- FILESTREAM in SQL Server: This article gives you an overview of the FILESTREAM feature. You can also learn a different way to enable this feature at the instance level with access configurations SQL Server中的FILESTREAM ：本文概述了FILESTREAM功能。 您还可以通过访问配置来学习在实例级别启用此功能的另一种方法
- Managing data with SQL Server FILESTREAM tables: Once you have enabled the FILESTREAM, you need to create the database for it with a dedicated filegroup. This article also shows the DML (update, delete) command for the FILESTREAM database使用SQL Server FILESTREAM表管理数据 ：启用FILESTREAM后，需要使用专用文件组为其创建数据库。 本文还显示了FILESTREAM数据库的DML（更新，删除）命令
- SQL Server FILESTREAM Database backup overview: It is an essential aspect for the DBA to understand the backup before enabling any new feature in SQL Server. This article gives you insights of the FILESTREAM of backups for the FILESTREAM database using the extended event sessionSQL Server FILESTREAM数据库备份概述 ：在启用SQL Server中的任何新功能之前，DBA理解备份是一个重要方面。 本文为您提供了使用扩展事件会话的FILESTREAM数据库备份的FILESTREAM的见解。
- Restore SQL Server FILESTREAM enabled database: This article walks you through the internals of database restore for the FILESTREAM enabled database. We cover both the full backup restore and the filegroup level restore also
- 还原启用SQL Server FILESTREAM的数据库 ：本文向您介绍启用FILESTREAM的数据库的数据库还原的内部结构。 我们还介绍了完整备份还原和文件组级别还原
It is the responsibility of DBA to recover the database in case of any unexpected issues such as data deletion, database corruption etc. In this article, we will cover various database recovery scenarios for the SQL Server FILESTREAM database.
万一发生数据删除，数据库损坏等意外问题，DBA有责任恢复数据库。在本文中，我们将介绍SQL Server FILESTREAM数据库的各种数据库恢复方案。
工作的例子 (Worked examples)
For this demonstration, we’ll use the ‘FileStreamDemoDB_test’ database in our instance.
对于此演示，我们将在实例中使用“ FileStreamDemoDB_test”数据库。
Currently, we have only one document in our FILESTREAM table.
目前，我们的FILESTREAM表中只有一个文档。
Let us insert one more record into it.
让我们再插入一条记录。
DECLARE @File varbinary (MAX);
SELECT
@File = CAST(
bulkcolumn as varbinary(max)
)
FROM
OPENROWSET(BULK 'C:\sqlshack\Stonehenge.jpg', SINGLE_BLOB) as MyData;
INSERT INTO DemoFileStreamTable_1
VALUES
(
NEWID(),
'Stonehenge Picture',
@File
)
Now we have two records in the test table.
现在，我们在测试表中有两个记录。
Below is the screenshot of the SQL Server FILESTREAM container.
下面是SQL Server FILESTREAM容器的屏幕快照。
We can put the database in Full recovery model using the below command.
我们可以使用以下命令将数据库置于完全恢复模式。
USE [master]
GO
ALTER DATABASE [FileStreamDemoDB_test] SET RECOVERY FULL WITH NO_WAIT
GO
Now take a filegroup level backup for the FILESTREAM database.
现在为FILESTREAM数据库进行文件组级别的备份。
BACKUP DATABASE [FileStreamDemoDB_test] FILEGROUP = N'DemoFileStream'
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\FileGroupBackup.bkp'
WITH NOFORMAT, NOINIT, NAME = N'FileStreamDemoDB_test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Now, let us generate the recovery scenario. We are going to delete the below-highlighted file from the FILESTREAM container. We do not get any prompt that the file is in use while deleting it from the filesystem.
现在，让我们生成恢复方案。 我们将从FILESTREAM容器中删除下面突出显示的文件。 从文件系统中删除文件时，没有任何提示该文件正在使用中的提示。
Remove this file from the FILESTEAM container manually.
手动从FILESTEAM容器中删除此文件。
Once we have deleted the file, let us rerun the query to check the available records in the SQL Server FILESTREAM container. We do not get any prompt that the file is in use while deleting it from the filesystem.
删除文件后，让我们重新运行查询以检查SQL Server FILESTREAM容器中的可用记录。 从文件系统中删除文件时，没有任何提示该文件正在使用中的提示。
Once we have deleted the record, We get the below error message.
删除记录后，我们将收到以下错误消息。
We cannot access the FILESTREAM database objects now. In this case, we need to restore the Full database backup to fix this error. Launch the database restore wizard and provide the database backup file path.
我们现在无法访问FILESTREAM数据库对象。 在这种情况下，我们需要还原完整数据库备份以修复此错误。 启动数据库还原向导，并提供数据库备份文件路径。
You get the warning message that it is going to perform a tail log backup before the restoration. The Tail log backup takes backup of the changes that are not backed up yet.
您收到警告消息，它将在还原之前执行尾日志备份。 尾日志备份将备份尚未备份的更改。
We do not want to take the tail-log backup at this point. We can uncheck the tick on ‘Take a tail-log backup before restore’.
我们现在不希望进行尾日志备份。 我们可以取消选中“还原前进行尾日志备份”的打勾。
You might get below error message if the database is in use because it cannot get the exclusive access on that database.
如果正在使用数据库，则可能会收到以下错误消息，因为它无法获得对该数据库的独占访问权限。
Therefore, put a check on the ‘Close existing connections to destination database’.
因此，请选中“关闭到目标数据库的现有连接”。
Once the database restoration is complete, rerun the query to check the records. We have access the database now, and table record is present as shown below.
数据库还原完成后，请重新运行查询以检查记录。 现在，我们可以访问数据库，并且表记录如下所示。
After the database restoration, you can see that in the filesystem containing the file is present which we deleted in previous steps.
恢复数据库后，您可以看到在包含该文件的文件系统中存在该文件，我们已在前面的步骤中将其删除。
使用FILESTREAM FILEGROUP级别备份进行恢复 (Recovery using the FILESTREAM FILEGROUP level backup)
In SQL Server FILESTREAM Database backup overview, we provided an overview of filegroup level backup for the FILESTREAM database. We can restore the deleted files using the filegroup level backup but we need to have below backups for the restoration
在“ SQL Server FILESTREAM数据库备份概述”中 ，我们提供了FILESTREAM数据库的文件组级别备份的概述。 我们可以使用文件组级别的备份来还原已删除的文件，但是我们需要以下备份来进行还原
- FILESTREAM Filegroup level backup FILESTREAM文件组级别的备份
- Tail-log backup 尾日志备份
Let us initiate the filegroup level backup now.
现在让我们启动文件组级别的备份。
Once the filegroup backup is complete, delete the record again from the filesystem container. We again received the below error message.
文件组备份完成后，再次从文件系统容器中删除记录。 我们再次收到以下错误消息。
Now, take the tail-log backup with SSMS.
现在，使用SSMS进行尾日志备份。
Select the option ‘Backup the tail of the log and leave the database in the restoring state’.
选择选项“备份日志的尾部并使数据库保持还原状态”。
After the tail-log backup, the database is in ‘Restoring’ state.
尾日志备份后，数据库处于“正在还原”状态。
Previously, we used the full backup to recover the file and resolve this error message.
以前，我们使用完整备份来恢复文件并解决此错误消息。
Now, let us restore the filegroup level backup and put the database in ‘NoRecovery’ mode. We can restore further transactions log when the database is in ‘NoRecovery’ mode. Click on ‘Options’ and put a check on ‘Leave the database non-operational and don’t roll back the uncommitted transactions (Restore with NoRecovery).’
现在，让我们还原文件组级别的备份，并将数据库置于“ NoRecovery”模式。 当数据库处于“ NoRecovery”模式时，我们可以还原其他事务日志。 单击“选项”，然后选中“保持数据库不运行并且不回滚未提交的事务（使用NoRecovery还原）”。
Once the filegroup level restore is completed, we need to restore the tail-log backup. We cannot restore the tail-log backup from the SSMS graphical wizard. We need to run the t-SQL command to do it.
文件组级别的还原完成后，我们需要还原尾日志备份。 我们无法从SSMS图形向导恢复尾日志备份。 我们需要运行t-SQL命令来执行此操作。
Run the below command in the master database to restore the tail-log backup.
在master数据库中运行以下命令以还原尾日志备份。
RESTORE LOG FileStreamDemoDB_test FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\backup3.trn'
Below is the output of the tail-log backup restore. The database is in Online status after this restore.
以下是尾日志备份还原的输出。 此还原后，数据库处于联机状态。
We have recovered the deleted file that we deleted earlier for this demo.
我们已经恢复了我们先前为该演示删除的已删除文件。
In the previous approach, we needed to take the complete database in the restoring mode to restore the tail-log backup. We might be using the database for the FILESTREAM as well as standard database activities. In this case, there will be downtime for the complete database. We might not want this situation especially if database is in the production. We can restore the filegroup level backup also in the enterprise edition of SQL Server.
在以前的方法中，我们需要以还原模式使用完整的数据库来还原尾日志备份。 我们可能将数据库用于FILESTREAM以及标准数据库活动。 在这种情况下，整个数据库将停机。 我们可能不希望出现这种情况，特别是如果数据库正在生产中。 我们也可以在企业版SQL Server中还原文件组级别的备份。
使用事务日志备份进行恢复 (Recovering using a transaction log backup)
Suppose the filegroup is corrupt and we cannot access it. For demo purpose, let us put filegroup in offline status with below command.
假设文件组已损坏，我们无法访问它。 出于演示目的，让我们使用以下命令将文件组置于脱机状态。
ALTER DATABASE FileStreamDemoDB_Test MODIFY FILE(NAME = DemoFiles, OFFLINE)
Check the status of the database files using the below command.
使用以下命令检查数据库文件的状态。
In the below image, check the status for the FILESTREAM database file, it is showing the status as ‘Offline’ for the FILESTREAM.
在下图中，检查FILESTREAM数据库文件的状态，该状态显示为FILESTREAM的“脱机”状态。
At this moment, we cannot access the FILESTREAM data. If we try to select the record from the table in the FILESTREAM filegroup, we get the below error message.
目前，我们无法访问FILESTREAM数据。 如果我们尝试从FILESTREAM文件组的表中选择记录，则会收到以下错误消息。
In the tail-log backup, the database becomes unavailable, therefore; we are taking a regular transactional log backup so that database remains online only.
因此，在尾日志备份中，数据库将变得不可用； 我们将定期进行事务日志备份，以便数据库仅保持联机状态。
Now let us start the database recovery process by restoring the filegroup backup and then the transactional log backup. In the Restore wizard, select the option ‘Restore Files and FIlegroups’.
现在，让我们先恢复文件组备份，然后再恢复事务日志备份，从而开始数据库恢复过程。 在“还原”向导中，选择“还原文件和文件组”选项。
We get the below error while restoring the FILESTREAM filegroup backup. We need to use Replace option while restoring the filegroup backup.
恢复FILESTREAM文件组备份时，出现以下错误。 恢复文件组备份时，我们需要使用“替换”选项。
To resolve this error, put a checkbox on the ‘overwrite the existing database (WITH REPLACE)’ and restore the database with ‘NORECOVERY’ clause.
要解决此错误，请选中“覆盖现有数据库（替换）”复选框，并使用“ NORECOVERY”子句还原数据库。
Click on ‘OK’ and database restores successfully now with the filegroup level restore.
单击“确定”，现在通过文件组级还原成功还原数据库。
We can do the restore of the log backup now. Run the below command in SSMS; we cannot restore this t-log backup with GUI.
我们现在可以还原日志备份。 在SSMS中运行以下命令； 我们无法使用GUI恢复此t-log备份。
use master
go
RESTORE LOG FileStreamDemoDB_test FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\backup6.trn'
Once the log backup restoration completes, you can verify the status of the files. It is showing ONLINE now.
日志备份还原完成后，您可以验证文件的状态。 现在正在显示在线。
Again, perform the database validation stuff. You can find the file now in the SQL Server FILESTREAM container. In the database also, you can see the record.
同样，执行数据库验证。 您现在可以在SQL Server FILESTREAM容器中找到该文件。 同样在数据库中，您可以看到记录。
结论 (Conclusion)
This article gives you an overall idea to recover the deleted objects using the full database restore and filegroup level restoration. It will help to recover the database in case of any unexpected issues. We will cover more on SQL Server FILESTREAM in my next article. Stay tuned!
本文为您提供了使用完整数据库还原和文件组级别还原来恢复已删除对象的总体思路。 万一发生任何意外问题，它将有助于恢复数据库。 在我的下一篇文章中，我们将详细介绍SQL Server FILESTREAM。 敬请关注！
目录 (Table of contents)
翻译自: https://www.sqlshack.com/sql-server-filestream-database-recovery-scenarios/
filestream
800
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
