Are non-modified FILESTREAM files excluded from DIFFERENTIAL backups?

Short answer seems to be “yes”.

I got this from a forum post today, so I thought I’d test it out. Basically, the discussion is whether we can somehow cut down backup sizes for filestream data (assumption is that filestream data isn’t modified very frequently). I’ve seen this a few times now, and often suggestions arises to somehow exclude the filestream data and fo file level backup of those files. But that would more or less leaves us with the problem of the “old” solution: potential inconsistency.

So I thought that perhaps diff backups can be helpful here? I.e., perhaps SQL Server might be smart enough to in a diff backup exclude the filestream files that were already in the most prior database backup? Sure seems so, according to below script. (I’m on the road now, so scrips could be neater…):

USE master
GO
IF DB_ID('fstest'IS NOT NULL DROP DATABASE fstest
GO

CREATE DATABASE fstest ON PRIMARY
NAME fstest_data,
FILENAME N'C:\DemoDatabases\DbFiles\a\fstest_data.mdf'),
FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
NAME FileStreamTestDBDocuments,
FILENAME N'C:\DemoDatabases\DbFiles\a\Documents')
LOG ON
NAME 'fstest_log',
FILENAME N'C:\DemoDatabases\DbFiles\a\fstest_log.ldf');
GO

--Baseline:
BACKUP DATABASE fstest TO DISK = 'C:\x\fst.bak' WITH INIT
--4.08 MB

BACKUP DATABASE fstest TO DISK = 'C:\x\fst_d.bak' WITH INITDIFFERENTIAL
--3.08 MB

CREATE TABLE fstest..t (
TestId UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE
,Name VARCHAR (25)
,
Document VARBINARY(MAXFILESTREAM);
GO

INSERT INTO fstest..t
VALUES(NEWID (), 'Hello there'CAST(REPLICATE(CAST('M' AS VARBINARY(MAX)), 1000000AS VARBINARY(MAX)));
GO 10

BACKUP DATABASE fstest TO DISK = 'C:\x\fst.bak' WITH INIT
--13 MB

BACKUP DATABASE fstest TO DISK = 'C:\x\fst_d.bak' WITH INITDIFFERENTIAL
--3.08

--Cleanup
IF DB_ID('fstest'IS NOT NULL DROP DATABASE fstest

One Reply to “Are non-modified FILESTREAM files excluded from DIFFERENTIAL backups?”

Leave a Reply

Your email address will not be published.