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
Although you had SORT_IN_TEMPDB = OFF
, that is not the only thing that uses tempdb. You also had set ONLINE = ON
.
Since the job ran 50 minutes before you had trouble, it may be that you had enough activity in that single transaction to fill tempdb
with row versioning data. This is described here:
http://technet.microsoft.com/en-us/library/ms179542.aspx
In part it says: "Online index operations use row versioning to isolate the index operation from the effects of modifications made by other transactions. ... Concurrent user update and delete operations during online index operations require space for version records in tempdb
."
EDIT: Actually a 200 MB tempdb
seems quite small for a database with a 44 GB table.
Best Answer
What was the specific error you received?
I don't believe FILESTREAM enabled databases have different restore settings than a typical database... a quick internet search doesn't return results to the contrary.
Questions for you:
Have you checked the size of the database (link to TSQL) and compared it to how much disk space is available on the destination server's drives (another link)?
Or use this code (msdn source):
SELECT * FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);
Are you using the WITH MOVE option to ensure the database files are allocated to specific drive(s) / mount-points which have enough free space?
It's certainly possible...
Are you actively adding any databases (or random files that shouldn't be there) to the destination drive(s)?
Is your destination drive C:?
Also, look into the use of WITH RESTART for your restores...