Keeping (Partial) Backups Small when using SQL Server FILESTREAM

Question

I have a database with nearly 1TB of FILESTREAM data that I don't need backed up (if the data was deleted it'd be recreated automatically in a couple of hours, so it just isn't important). Most of the data is changed every couple of days, so differential backups wouldn't really help keep the size down.

I had the backups working the way I needed by setting Recovery Mode to Full, creating a separate FILEGROUP for the FILESTREAM, then taking backups of only the "Primary" FILEGROUP. The problem this caused was that the log file (which also gets backed up) is now unnecessarily large because it includes the FILESTREAM data.

SIMPLE Recovery Mode takes away my ability to do backups of specific FILEGROUPs, so I don't think that will be an option either.

My thoughts are to just move the FILESTREAM data to a separate database, but now I'm losing referential integrity and surely inheriting a host of other issues as well.

Is there any way to create partial backups in Simple recovery mode (without setting the FILESTREAM table to read only)? If not, are there any other sane solutions to my problem?

Answer 1

The problem this caused was that the log file (which also gets backed up) is now unnecessarily large because it includes the FILESTREAM data.

I'm not sure if you mean the log file itself is too large or that the log file backups become too large.

If it's the former then how often were you backing the log up? Depending on the application design you may be able to reduce the size by backing up more frequently (and every 5 minutes is not too frequently). However if you were already doing that and it was still ballooning then you're probably out of luck. Why is the large log file a problem again?

If it's the latter - it sounded like you were happy to continue with simple recovery model and no point in time restores if it let you have smaller backups; in which case stay in full mode and discard your log backups.

Answer 2

A solution for a database set to recovery mode SIMPLE is having the FILESTREAM data in a read-only file group (which isn't your ideal option), and then backing up only the read/write file groups with DIFFERENTIAL like this:

BACKUP DATABASE [name] READ_WRITE_FILEGROUPS TO DISK = '' WITH DIFFERENTIAL, COMPRESSION;

It will get any data that has changed in any read/write filegroups. That's the easiest, out of the box, that you can keep partial backups manageable without getting the FILESTREAM data. It would however require that the load process for the aforementioned data would need to modify the filegroup to be read/write, load any additional data, and then set to read only again. Certainly not ideal.

Answer 3

I feel dirty providing this as an option, but if you choose to segregate the FILESTREAM data into its own database, you could maintain RI between the tables in the separate dbs by way of triggers:

Triggers -> Remarks -> Limitations:
A trigger is created only in the current database; however, a trigger can reference objects outside the current database.

Expect performance issues and a section of your scalp to become hairless after pulling out tufts of your head fur in frustration, but theoretically you could do this. I don't recommend this approach on any level, instead I strongly suggest you increase the frequency of your tlog backups and/or switch to the bulk-logged recovery model and see how much space that saves you, BUT this is a possible solution. Really you'd need to weigh the benefit of separating out this data and dealing with a Frankensteinian database design, but it's an option.

...I have to go take a shower now...

Answer 4

I know this question is already answered, but there is another solution that might help others. Recently I learnt from Brent Ozar's blog that there is an option to discard your log backups right away:

BACKUP LOG MyDb TO DISK='NUL:'

So you can leave your database in Full Recovery Mode and do backups of filegroups. When your transaction log gets too big, then just issue the backup log command and you are done.