Using SSMS, you cannot chain a restore of the backups in one operation. You would have to do multiple restores. You'll want to use T-SQL in order to be more efficient.
--Restore the most recent full backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to full backup'
WITH NORECOVERY, STATS=10 --If only restoring the full, change to RECOVERY
--Restore the most recent diff backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to diff backup'
WITH RECOVERY, STATS=10
More info on RESTORE: http://msdn.microsoft.com/en-us/library/ms186858.aspx
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
Best Answer
You have to restore the database first with the norecovery switch, then restore the other file group. Then roll the transaction log forward then bring the database online.
This requires a minimum of three RESTORE DATABASE commands to do.