Sql-server – Backup/restore of a database that has a filestream

If your database is in simple recovery model, then you cannot perform a point-in-time recovery. If the database is not in simple-recovery model and you have log backups right upto the point of failure without a break in the log chain, then you can take a tail-log backup (if LDF is intact and the command succeeds) as pointed out by yrushka and perform a point-in-time recovery of the database without losing any data. If there are no log backups till now, then nothing can be done unfortunately.

Another option is that if you have the database in full/bulk logged-recovery model and no log backups have been taken, then the transaction log can be read using tools (eg. from Idera, LiteSpeed) which will allow you to generate the commands from the transaction log file which can be replayed on the database once it is restored.

Additionally, if you have a full backup and all the log backups till now, then you can restore them till the latest available log backup. Then using log reader tools above, you can generate the rest of the commands from the LDF file and replay them against the database.

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