filestream
The SQL Server FILESTREAM feature in SQL Server allows storing the large documents files directly into the file systems. In my previous articles, we explored the concepts of FILESTREAM feature in SQL Server. We also learned the folder structures, metadata information and the process of garbage collection. In this article, we are going to learn about the backup and restores in SQL Server.
SQL Server中SQL Server FILESTREAM功能允许将大型文档文件直接存储到文件系统中。 在我以前的文章中，我们探讨了SQL Server中FILESTREAM功能的概念。 我们还学习了文件夹结构，元数据信息和垃圾回收过程。 在本文中，我们将学习有关SQL Server中的备份和还原的信息。
Before we move further below are the pre-requisites for this article.
在继续进行下面的操作之前，请先确定本文的前提条件。
- Enable the FILESTREAM feature at the instance level (either during the SQL Server installation or by the SQL Server Configuration Manager) 在实例级别上启用FILESTREAM功能（在SQL Server安装过程中或通过SQL Server配置管理器）
- Configure for the FILESTREAM access level 配置FILESTREAM访问级别
We have the following FILESTREAM database and the content inside it.
我们有以下FILESTREAM数据库及其中的内容。
SELECT [FileId]
,[FileName]
,[File]
FROM [FileStreamDB].[dbo].[DemoFileStreamTable_1]
You can find below file in the FILESTREAM container.
您可以在FILESTREAM容器中找到以下文件。
In the FILESTREAM database, We need to specify below things you do differently from a standard database in SQL Server.
在FILESTREAM数据库中，我们需要在下面指定与SQL Server中的标准数据库不同的操作。
- Specify a FILESTREAM filegroup 指定一个FILESTREAM文件组
- Create a FILESTREAM data file into that filegroup 在该文件组中创建一个FILESTREAM数据文件
Backup and restore is a critical task for database administrators. The primary objective is to restore the database in case of any failure, data deletion. The same concepts apply to FILESTREAM enabled databases as well. If someone deleted the files accidentally from the file share, you should be able to recover that.
对于数据库管理员来说，备份和还原是一项关键任务。 主要目标是在发生任何故障（数据删除）的情况下还原数据库。 相同的概念也适用于启用了FILESTREAM的数据库。 如果有人不小心从文件共享中删除了文件，则应该可以恢复该文件。
In SQL Server, when we execute a full backup on the SQL Server FILESTREAM database, it takes the backup of following items:
在SQL Server中，当我们在SQL Server FILESTREAM数据库上执行完全备份时，它将备份以下各项：
- Backup of the data files (.MDF,.NDF) of the database 备份数据库的数据文件（.MDF，.NDF）
- Transaction log backup 交易日志备份
- FILESTREAM file group backup along with the associated files FILESTREAM文件组备份以及相关文件
SQL Server database backup includes the FILESTREAM filegroup and the files inside it. Let us first execute the full backup of the database.
SQL Server数据库备份包括FILESTREAM文件组和其中的文件。 让我们首先执行数据库的完整备份。
- Right click on the database ->Tasks ->Back Up. 右键单击数据库->任务->备份。
- In the backup database wizard, specify the backup type ‘Full’ and the backup location. This backup location should have sufficient free space 在备份数据库向导中，指定备份类型“完整”和备份位置。 该备份位置应具有足够的可用空间
- Click on ‘OK’ to execute the database backup. We can also generate the script using the ‘Script Actions to new query window’. Let us generate the script. 单击“确定”以执行数据库备份。 我们还可以使用“脚本操作到新查询窗口”生成脚本。 让我们生成脚本。
We get the below script to take a full backup. Backup of SQL Server FILESTREAM enabled database is similar to a standard database backup.
我们得到以下脚本进行完整备份。 启用SQL Server FILESTREAM的数据库的备份类似于标准数据库的备份。
BACKUP DATABASE [FileStreamDB]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\FileStreamDB.bak'
WITH NOFORMAT, NOINIT, NAME = N'FileStreamDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Go
Let us execute this command and observe the output.
让我们执行此命令并观察输出。
14 percent processed.
Processed 432 pages for database ‘FileStreamDB’, file ‘FileStreamDB’ on file 9.
20 percent processed.
30 percent processed.
….
81 percent processed.
91 percent processed.
Processed 2041 pages for database ‘FileStreamDB’, file ‘DemoFiles’ on file 9.
Processed 2 pages for database ‘FileStreamDB’, file ‘FileStreamDB_log’ on file 9.
100 percent processed.
BACKUP DATABASE successfully processed 2475 pages in 1.578 seconds (12.249 MB/sec).
14％已处理。
处理了432页的数据库'FileStreamDB'，文件'FileStreamDB'上的文件9。
已处理20％。
30％已处理。
…。
81％已处理。
91％已处理。
已处理2041页的数据库'FileStreamDB'，文件'DemoFiles'在文件9中。
处理了2页的数据库'FileStreamDB'，文件'FileStreamDB_log'到文件9。
100％已处理。
BACKUP DATABASE在1.578秒（12.249 MB /秒）中成功处理了2475页。
Now let us compare the backup statement output with the database files (Right click on database and click properties and then go to files)
现在，让我们将备份语句输出与数据库文件进行比较（右键单击数据库，然后单击属性，然后转到文件）
In the above image, you can notice that it took backup of the file ‘Demofiles’, which is a ‘FILESTREAM data’ file.
在上图中，您可以注意到它备份了文件“ Demofiles”，这是一个“ FILESTREAM数据”文件。
IF the files contained in the FILESTREAM filesystem is open in another program, you get the below error message in the backup operation.
如果FILESTREAM文件系统中包含的文件在另一个程序中打开，则在备份操作中会出现以下错误消息。
Msg 3056, Level 16, State 1, Line 1
The backup operation has detected an unexpected file in a FILESTREAM container.
The backup operation will continue
and include file ‘C:\sqlshack\FileStream\DemoFiles\97f720ed-afbf-413d-8f5c-5b56d4736984\8f62d1b7-7f8b-4f98-abe9-a06b4faa1d2e\00000024-00000588-000c\ C:\sqlshack\FileStream\DemoFiles\97f720ed-afbf-413d-8f5c-5b56d4736984\8f62d1b7-7f8b-4f98-abe9-a06b4faa1d2e\~$000024-00000588-000c’.
消息3056，第16级，状态1，第1行
备份操作已在FILESTREAM容器中检测到意外文件。
备份操作将继续
并包含文件'C：\ sqlshack \ FileStream \ DemoFiles \ 97f720ed-afbf-413d-8f5c-5b56d4736984 \ 8f62d1b7-7f8b-4f98-abe9-a06b4faa1d2e \ 00000024-00000588-000c \ C：\ sqlshack \ FileStream \ Demoededs \ 97 afbf-413d-8f5c-5b56d4736984 \ 8f62d1b7-7f8b-4f98-abe9-a06b4faa1d2e \〜$ 000024-00000588-000c'。
This error message shows that there is another file detected in the SQL Server FILESTREAM container. However, the backup operation will continue to run. In error, you can notice the unexpected file name (‘\~$000024-00000588-000c’). When we open any file in windows, it opens a hidden file into the same folder. By default, the hidden file is not visible in the folder. Open the FILESTREAM container folder and in the menu bar, click on the ‘Hidden items’. This option will allow viewing the hidden file in the folder.
此错误信息表明在SQL Server FILESTREAM容器中检测到另一个文件。 但是，备份操作将继续运行。 错误地，您会注意到意外的文件名（' \〜$ 000024-00000588-000c'）。 当我们在Windows中打开任何文件时，它将在同一文件夹中打开一个隐藏文件。 默认情况下，隐藏文件在文件夹中不可见。 打开FILESTREAM容器文件夹，然后在菜单栏中单击“隐藏的项目”。 此选项将允许查看文件夹中的隐藏文件。
Now, notice in the FILESTREAM container, you can notice the hidden file ‘\~$000024-00000588-000c’ in this folder. SQL Server backup does not recognise this file there it says that there is an unexpected file in the FILESTREAM container. SQL Server backup will also include this file during the backup activity.
现在，请注意FILESTREAM容器中的隐藏文件夹'\〜$ 000024-00000588-000c'。 SQL Server备份无法识别该文件，它表示FILESTREAM容器中有意外文件。 在备份活动期间，SQL Server备份还将包括此文件。
We will look more in details about the SQL Server FILESTREAM database backup using the extended event ‘backup_restore_progress_trace’. You can use this extended event from SQL Server 2016.
我们将使用扩展事件“ backup_restore_progress_trace”详细了解有关SQL Server FILESTREAM数据库备份的详细信息。 您可以从SQL Server 2016使用此扩展事件。
You need to create the external event session from the extended event sessions. In the Management folder, Expand ‘Extended Extents’ and right click on the ‘Sessions’.
您需要从扩展事件会话中创建外部事件会话。 在“管理”文件夹中，展开“扩展范围”，然后右键单击“会话”。
Enter a name for the extended event session.
输入扩展事件会话的名称。
Search the extended event ‘backup_restore_progress_trace’ from the event library and take it towards the right window and click ‘Next’.
从事件库中搜索扩展事件“ backup_restore_progress_trace” ，并将其移到右侧窗口，然后单击“下一步”。
Now move to the ‘Specify Session Data Storage’ page. We can save it the event file for later use or work with the ring buffer to get the recent data. We will choose the option ‘work with only the most recent data (ring_buffer-data).
现在移至“指定会话数据存储”页面。 我们可以将其保存为事件文件以供以后使用，或与环形缓冲区一起使用以获取最新数据。 我们将选择选项'仅处理最新数据（ring_buffer-data）。
Select the below options as shown in the screenshot.
选择以下选项，如屏幕截图所示。
- Start the session immediately after the extended event session. 在扩展事件会话之后立即启动会话。
- Watch live data on screen once it is captured 捕获后在屏幕上观看实时数据
Execute the backup for the FILESTREAM database again and observe the output in the extended event session.
再次执行FILESTREAM数据库的备份，并观察扩展事件会话中的输出。
You can notice that below operations with the backups.
您会注意到以下对备份的操作。
- Once we execute the database backup, it takes the S lock and bulk-op lock on the database. Since it is a full backup, it clears the differential bitmaps. 一旦执行数据库备份，它将对数据库进行S锁定和批量操作锁定。 由于它是完整备份，因此会清除差分位图。
- It issues CHECKPOINT on the database and scans allocation bitmap. It also shows the first and last LSN of the database for the backup. 它在数据库上发出CHECKPOINT并扫描分配位图。 它还显示了备份数据库的第一个和最后一个LSN。
- Before copying the data for the backup, it forcefully applies the first CHECKPOINT and starts scanning the bitmap. 在复制用于备份的数据之前，它会强制应用第一个CHECKPOINT并开始扫描位图。
- It writes the backup metadata after a couple of force checkpoints. 它在几个强制检查点之后写入备份元数据。
Below you can see it reads the.MDF file for the FILESTREAM database.
在下面您可以看到它读取FILESTREAM数据库的.MDF文件。
Now it copies the data into the backup file. You can see in below screenshot that it copies the data file followed by the FILESTREAM data.
现在，它将数据复制到备份文件中。 您可以在下面的屏幕快照中看到它复制了数据文件，然后复制了FILESTREAM数据。
In the end, it copies the transaction log, and backup database successful message is displayed.
最后，它复制事务日志，并显示备份数据库成功消息。
In the extended event session, you can file for the FILESTREAM it performs the below steps for the backup.
在扩展事件会话中，您可以归档FILESTREAM，它执行以下备份步骤。
- Data files 资料档案
- Log Files 日志文件
- SQL Server FILESTREAM backup. SQL Server FILESTREAM备份。
SQL Server中SQL Server FILESTREAM文件组备份 (SQL Server FILESTREAM filegroup backup in SQL Server)
In SQL Server we can take FILEGROUP level backups as well. As you know that to use the FILESTREAM feature, we need to create a particular filegroup. Therefore, if we do not want to take a full backup, we can take FILESTREAM filegroup backup as well. It contains the FILESTREAM filegroup files.
在SQL Server中，我们也可以进行FILEGROUP级别的备份。 如您所知，要使用FILESTREAM功能，我们需要创建一个特定的文件组。 因此，如果我们不想进行完整备份，则也可以进行FILESTREAM文件组备份。 它包含FILESTREAM文件组文件。
In the backup database window, click on ‘Files and FileGroup’. It opens another window and allows selecting the filegroup for which we want to take the backup. In the following image, you can see we have selected the FILESTREAM filegroup.
在备份数据库窗口中，单击“文件和文件组”。 它打开另一个窗口，并允许选择我们要为其备份的文件组。 在下图中，您可以看到我们已选择FILESTREAM文件组。
Click ‘Ok’ and generate the script for filegroup backup.
单击“确定”并生成用于文件组备份的脚本。
BACKUP DATABASE [FileStreamDB]
FILEGROUP = N'DemoFileStream'
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\FileStreamDB.bak'
WITH NOFORMAT, NOINIT, NAME = N'FileStreamDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
It gives the below output message for the filegroup level FILESTREAM database backup.
它为文件组级别的FILESTREAM数据库备份提供以下输出消息。
结论： (Conclusion:)
In this article, we explored the various aspects of backup SQL Server FILESTREAM database including full and filegroup backups. We also learned about the internals of FILESTREAM database backup using the sessions of the extended event. We will cover the FILESTREAM database restore in my next article.
在本文中，我们探讨了备份SQL Server FILESTREAM数据库的各个方面，包括完整备份和文件组备份。 我们还使用扩展事件的会话了解了FILESTREAM数据库备份的内部信息。 我们的下一篇文章将介绍FILESTREAM数据库还原。
目录 (Table of contents)
翻译自: https://www.sqlshack.com/sql-server-filestream-database-backup-overview/
filestream
815
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
