THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

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

Published Friday, May 14, 2010 9:38 AM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

James Luetkehoelter said:

Nice validation!

May 14, 2010 9:25 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement