Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Could you do the full restore once so you have some data from the [BinaryContent] FILEGROUP and then do a restore of the Primary filegroup when you want to up date it? –  jgardner04 May 23 '12 at 19:18
    
@jgardner04: that doesn't seem to work. The database ends up in an inconsistent state if I first do a full restore, followed by a restore of the backup containing only the Primary file group (Error message: "The database cannot be recovered because the log was not restored (...) the database could not be brought online because one or more RESTORE steps are needed"). –  Nailuj May 24 '12 at 7:16
    
Is your access to dbo.BinaryContent always via stored procedures? How many are involved? –  Mark Storey-Smith May 24 '12 at 16:42
    
@MarkStorey-Smith: the database is mainly accessed using regular queries, through NHibernate (both from an ASP.NET web application and a Windows forms application). How is that relevant? –  Nailuj May 24 '12 at 22:33
2  
If your access was via stored procedures we could apply an approach from partial availability/piecemeal restore to check which filegroups are online. To be honest, at 12GB it's really not worth working around, just to do the full restore. –  Mark Storey-Smith May 24 '12 at 22:54
show 3 more comments

2 Answers

up vote 7 down vote accepted

What you're trying to do would leave the database in a (transactionally) inconsistent state, hence it isn't possible.

The Partial Database Availability whitepaper is a useful reference guide and includes an example of how to check whether a particular table or file is online. If your data access were through stored procedures, you could relatively easily incorporate that check.

One alternative (but somewhat hacky) approach that might be worth a look in your scenario would be to hide the table and replace it with a view.

-- NB: SQLCMD script
:ON ERROR EXIT
:setvar DatabaseName "TestRename"
:setvar FilePath "D:\MSSQL\I3\Data\"

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SET NOCOUNT ON;
GO

USE master;
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'$(DatabaseName)')
  DROP DATABASE $(DatabaseName)
GO

CREATE DATABASE $(DatabaseName) 
ON PRIMARY 
  (
  NAME = N' $(DatabaseName)'
  , FILENAME = N'$(FilePath)$(DatabaseName).mdf'
  , SIZE = 5MB
  , MAXSIZE = UNLIMITED
  , FILEGROWTH = 1MB
  ) 
, FILEGROUP [FG1] DEFAULT
  ( 
  NAME = N' $(DatabaseName)_FG1_File1'
  , FILENAME = N'$(FilePath)$(DatabaseName)_FG1_File1.ndf'
  , SIZE = 1MB
  , MAXSIZE = UNLIMITED
  , FILEGROWTH = 1MB 
  ) 
, FILEGROUP [FG2] CONTAINS FILESTREAM
  ( 
  NAME = N'$(DatabaseName)_FG2'
  , FILENAME = N'$(FilePath)Filestream'
  )
LOG ON 
  ( 
  NAME = N'$(DatabaseName)_log'
  , FILENAME = N'$(FilePath)$(DatabaseName)_log.ldf'
  , SIZE = 1MB
  , MAXSIZE = UNLIMITED
  , FILEGROWTH = 1MB
  )
GO

USE $(DatabaseName);
GO

CREATE TABLE [dbo].[BinaryContent](
    [BinaryContentID] [int] IDENTITY(1,1) NOT NULL
    , [FileName] [varchar](50) NOT NULL
    , [BinaryContentRowGUID] [uniqueidentifier] ROWGUIDCOL UNIQUE DEFAULT (NEWSEQUENTIALID()) NOT NULL
  , [FileContentBinary] VARBINARY(max) FILESTREAM  NULL
) ON [PRIMARY] FILESTREAM_ON [FG2]
GO 

-- Insert test rows
INSERT
  dbo.BinaryContent
  (
  [FileName]
  , [FileContentBinary]
  )
VALUES
  (
  CAST(NEWID() AS VARCHAR(36))
  , CAST(REPLICATE(NEWID(), 100) AS VARBINARY)
  );
GO 100

USE master;
GO

-- Take FILESTREAM filegroup offline
ALTER DATABASE $(DatabaseName)
MODIFY FILE (NAME = '$(DatabaseName)_FG2', OFFLINE)
GO

USE $(DatabaseName);
GO

-- Rename table to make way for view
EXEC sp_rename 'dbo.BinaryContent', 'BinaryContentTable', 'OBJECT';
GO

-- Create view to return content from table but with NULL FileContentBinary
CREATE VIEW dbo.BinaryContent
AS

SELECT
  [BinaryContentID]
    , [FileName] 
    , [BinaryContentRowGUID]
  , [FileContentBinary] = NULL
FROM
  [dbo].[BinaryContentTable];
GO

-- Check results as expected
SELECT TOP 10
  *
FROM
  dbo.BinaryContent;
GO
share|improve this answer
add comment

You could isolate the table with a FILESTREAM in a separate database and create a reference to it in the PRODUCTION database using a view.

This would allow you to do what you want without resorting to hacks.

share|improve this answer
    
This was going to be my approach, but I then came up against issues of maintaining referential integrity between the databases, as triggers are generally not supported with filestream tables: dba.stackexchange.com/questions/58208/… –  John J Smith Feb 5 at 9:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.