是否可以创建一个虚拟文件流文件组来仅研究mdf内容? [英] Is it possible to create a dummy filestream filegroup to study mdf content only?

问题描述

我的应用程序具有通过以下方式从备份文件创建的数据库:

My application has a database that I create from a backup file in this way:

-- create empty db
CREATE DATABASE MyNewDB

-- restore on the empty db from file    
RESTORE DATABASE MyNewDB
FROM DISK = 'c:\Temp\MyNewDB.bak'
WITH REPLACE,
MOVE 'MyDB_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.EXPRESS2008R2\MSSQL\DATA\MyNewDB.mdf',
MOVE 'MyDB_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.EXPRESS2008R2\MSSQL\DATA\MyNewDB_log.LDF',
MOVE 'MyDBFS' TO 'C:\FileStreamData\MyNewDBFS'

我使用filestream文件组仅存储blob，基本上，我有一个表在其中存储文件，还有一个blob列，其中包含保存为SQL Server Filestream数据的二进制数据.

I use the filestream filegroup to store the blobs only, basically I have a single table where I store files there and a single blob column containing the binary data that is saved as SQL Server Filestream data.

随着客户继续使用该应用程序，文件流部分变得非常大，可能是mdf为500MB，文件流为60GB

As customers keep using the app the filestream part becomes huge, it can be that mdf is 500MB and filestream is 60GB

通常要在开发机器上调试问题，我需要传输仅表"("500MB")和不是blob"("60GB").

Often to debug an issue on the development machine I need to transfer "only the tables" ("500MB") and "not the blobs" ("60GB").

我要做的是复制数据库，并在复制的数据库上，我在文件表中将blob列设置为NULL，然后运行

What I do is duplicating the database and on the duplicated database, I set the blob column to NULL in the files table and then I run

CHCEKPOINT

然后我等待垃圾收集器完成其工作，然后可以进行备份并保存一个小文件.

I then wait for the garbage collector to finish its job and then I can take a backup and have a small file.

有时候我没有空间来还原备份副本，或者我没有时间去做.

Sometimes I do not have space to restore a copy of the backup or I do not have time to do it.

所以我很想做，但是我没有找到所有网络上的解决方案:

So what I'd love to be able to do, but I did not find a solution in all the web is:

1. 仅复制.mdf + .ldf(包含数据)

以某种方式创建假文件流数据"(SQL Server将在附加数据时接受)

Somehow create "fake filestream data" (data that SQL Server will accept on attach)

附加.mdf，.ldf和伪造的文件流数据以创建测试数据库

Attach .mdf, .ldf and fake filestream data to create a test database

当然，我不希望Blob上的查询有效，但是其他表上的所有查询都是这样.

Of course, I do not expect the queries on the blobs to work, but all the queries on the other tables, yes.

有没有一种方法(甚至可能使用3d派对工具)来实现我所需要的?

Is there a way (may be even using a 3d party tool) to achieve what I need?

推荐答案

将其移动到其他数据库

如果是我，我会考虑将Blob数据分解到另一个数据库中.但是我不确定您当前的结构，或者它的实用性.但是，您仍然可以在原始数据库中拥有一个视图，该视图从新数据库中的表中进行选择，并且/或者重新组合每个表中的多个表.

If it was me, I'd consider breaking the blob data over into another database. But I'm not certain of your current structure, or how practical that is. However, you could still have a view in the original database that selects from the table in the new database, and/or recombines multiple tables from each.

编写数据库脚本

另一个并非完全漂亮的选择是编写数据库脚本. Management Studio可以创建一个脚本文件，该脚本文件在运行时将创建表并插入值.例如，如果您需要将数据从SQL 2014数据库复制到SQL 2008或类似数据库中，则可能有必要.通常，您可以对脚本编写内容进行一些选择. (右键单击数据库，然后选择任务"->生成脚本"以拉出向导.)

Another not entirely pretty option is to script the database. Management studio can create a script file that when run will create tables and insert values. This for example, might be necessary if you need to get a copy of data from a SQL 2014 database into SQL 2008 or etc. Generally, you can make some choices as to what is scripted out. (Right click on database and choose Tasks -> Generate Scripts to pull up a wizard.)

导出数据

您可以导出数据并导入所说的数据，而不是还原备份.这样做时，您可以选择要包含的数据.您可以跳过Blob表或Blob列.

Instead of restoring a backup, you could export the data and import said data. When doing this you get to choose what data is included. You could skip blob tables or skip blob columns.

片餐恢复

最后，您可能还希望在仅部分文件或部分文件组的逐段恢复中看到此链接. https://docs.microsoft.com/zh-cn/sql/relational-databases/backup-restore/example-piecemeal-restore-of-only-some-filegroups-full-recovery-model

Lastly, you may also want to see this link on piecemeal recovery of only some file or only some file groups. https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/example-piecemeal-restore-of-only-some-filegroups-full-recovery-model

文件组A和C的在线还原. 由于它们的数据未损坏，因此不必从备份中还原这些文件组，但必须对其进行还原才能使其联机. 数据库管理员会立即恢复A和C.

Online restore of filegroups A and C. Because their data is undamaged, these filegroups do not have to be restored from a backup, but they do have to be recovered to bring them online. The database administrator recovers A and C immediately.

RESTORE DATABASE adb FILEGROUP='A', FILEGROUP='C' WITH RECOVERY  
RESTORE DATABASE adb FILEGROUP='B' FROM backup2b WITH RECOVERY  

从理论上讲，您将备份空白数据库的文件流文件组，然后在还原时尝试单独使用该文件组.但是，我不确定这条路线是否适合您.如果您的Blob位于相同的表，相同的数据库中，则sql可能会存储一些数据，以将这些数据缝合在一起.恢复时，SQL可能会检查Blob数据是否一致和/或兼容?

In theory here you'd backup the filestream filegroup of a blank database, and then later try to use that separately when restoring. However, I'm not sure if this route will work for you. If your blobs are on the same tables, in the same database, then sql probably stores some data for stitching these back together. On restore, sql might check that the blob data is consistent and/or compatible?

结论

我真的认为，将您的Blob分解到自己的表中，然后将其移动到单独的数据库中是我的本事.

I really think that breaking your blobs out into their own tables and then moving them to a separate database is what I would do in your shoes.

您可能无法直接连接到这些数据库，或者上面提到的脚本选项，或者SQL任务导入数据可能是选项.

You probably can't directly connect to these databases, or else the scripting option mentioned above, or the SQL task import data could be options.

但是，仍然有sql导出数据选项.您可以构建一个SSIS包，该包仅导出所需的数据，然后可以将其重新导入.

But that said, there is still the sql export data option. You could build a SSIS package that only exports the data you want, which could then be re-imported.

这篇关于是否可以创建一个虚拟文件流文件组来仅研究mdf内容?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！