Differential backup on Filestream enabled database

Question

Differential backup on Filestream enabled database

Dizzy Desi

Ten Centuries

Points: 1031
More actions
August 3, 2010 at 3:42 pm

#238435

We have a very large filestream-enabled database.
In order to save disk space on the server and make sure that backups complete in a timely manner (8-10 hours now, and the db is expected to continue growing quickly), I am performing full backups once per week. Full backups include the NTFS filestream data of course. I am thinking about adding differential backups daily, but I can't find any documentation that states the effect of differential backups on filestream-enabled databases -- whether or not the NTFS changes are included in the differential backups.
Can anyone find any definitive documentation on differential backups with filestreams?
Thanks,
Desiree

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

This is what the MSDN documentation states.

you can use all backup and recovery models with FILESTREAM data, and the FILESTREAM data is backed up with the structured data in the database. If you do not want to back up FILESTREAM data with relational data, you can use a partial backup to exclude FILESTREAM filegroups

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

jacob sebastian SSChampion Points: 11822 More actions · Answer 2

The backup/restore operations of a FILESTREAM database is expected to be the same as NON-FILESTREAM databases.

I just tested this and verified that the differential backup includes FILESTREAM data files as well. You can see the details in this post: SQL Server FILESTREAM feature and Differential Backups

.

Dizzy Desi Ten Centuries Points: 1031 More actions · Answer 3

Thank you both for your replies. I was not sure if a differential backup was considered the same thing as a partial backup. I am not able to get to many websites via my work computer, so I will check your links from home. That may have aided my search, but I typically don't think about work once I get home 😀

jacob sebastian SSChampion Points: 11822 More actions · Answer 4

Differential backups and partial backups are not the same.

.

HowardW One Orange Chip Points: 29109 More actions · Answer 5

Depending on how often you perform updates to Filestream objects, you should bear in mind that all FILESTREAM objects that have been updated since the last full backup will be included in each differential backup (not just the portion of the file that has changed).

Dizzy Desi Ten Centuries Points: 1031 More actions · Answer 6

HowardW (8/5/2010)
Depending on how often you perform updates to Filestream objects, you should bear in mind that all FILESTREAM objects that have been updated since the last full backup will be included in each differential backup (not just the portion of the file that has changed).

That's very good to know. I will have to pay attention to the size of my differential backups once I get them going and the users start entering more data again, so that I can see if it's really doing me any good (time and file size) versus the full backup.

Thanks,

Desiree