Is it possible to create a dummy filestream filegroup to study mdf content only?

Question

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'

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.

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

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

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. Copy the .mdf + .ldf only (that contain the data)

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

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

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

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

Answer 1

Moving it to a different Database

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.

Scripting the Database

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.)

Exporting the Data

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.

Piece-meal recovery

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

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  

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?

Conclusion

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.

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.

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.