Context
We are developing a system with a large-ish database in the bottom. It is an MS SQL database running on SQL Server 2008 R2. The total size of the database is about 12 GB.
Out of these, approximately 8.5 GB is in a single table BinaryContent
. As the name suggests, this is a table where we store simple files, of any kind, directly in the table as a BLOB. Recently we've been testing the possibility to move all these files out of the database to the file system using FILESTREAM.
We did the necessary modifications to our database without any problems, and our system is still working fine after the migration. The BinaryContent
table looks roughly like this:
CREATE TABLE [dbo].[BinaryContent](
[BinaryContentID] [int] IDENTITY(1,1) NOT NULL,
[FileName] [varchar](50) NOT NULL,
[BinaryContentRowGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL
) ON [PRIMARY] FILESTREAM_ON [FileStreamContentFG]
ALTER TABLE [dbo].[BinaryContent] ADD [FileContentBinary] [varbinary](max) FILESTREAM NULL
ALTER TABLE [dbo].[BinaryContent] ADD CONSTRAINT [DFBinaryContentRowGUID] DEFAULT (newsequentialid()) FOR [BinaryContentRowGUID]
With everything residing in the PRIMARY
file group, except the field FileBinaryContent
which is in a separate file group FileStreamContentFG
.
Scenario
From a developer's point of view, we would often like a fresh copy of the database from our production environment, to be able to work the the latest data. In those cases, we are rarely interested in the files stored in BinaryContent (now using FILESTREAM).
We have this almost working as we'd like. We back up the database, without the file stream like this:
BACKUP DATABASE FileStreamDB
FILEGROUP = 'PRIMARY'
TO DISK = 'c:\backup\FileStreamDB_WithoutFS.bak' WITH INIT
And restore it like this:
RESTORE DATABASE FileStreamDB
FROM DISK = 'c:\backup\FileStreamDB_WithoutFS.bak'
This seems to be working OK, and our system works as long as we avoid the parts that use the FileBinaryContent
field. We can for instance run the following query without a problem:
SELECT TOP 10 [BinaryContentID],[FileName],[BinaryContentRowGUID]
--,[FileContentBinary]
FROM [dbo].[BinaryContent]
Naturally, if I un-comment the line above, including FileContentBinary
in the query, I get an error:
Large object (LOB) data for table "dbo.BinaryContent" resides on an offline filegroup ("FileStreamContentFG") that cannot be accessed.
Our system handles files where the content is set to null
, so what I would like to do is something like this:
UPDATE [dbo].[BinaryContent]
SET [FileContentBinary] = null
But this of course gives me the same error as above. At this point I'm stuck.
Question
Is there any way I can restore the database without having to also restore everything from the FileStreamContentFG
file group? Either by updating the values to null as I'm trying above, or default to to null when the file is missing or something?
Or am I perhaps approaching the problem in the wrong way?
I'm a developer by nature and does not have much knowledge as a DBA, so do excuse me if I'm overlooking some trivial thing here.