patternsqlMinor
Keeping (Partial) Backups Small when using SQL Server FILESTREAM
Viewed 0 times
keepingbackupsfilestreamsqlpartialsmallusingwhenserver
Problem
I have a database with nearly 1TB of
I had the backups working the way I needed by setting Recovery Mode to
My thoughts are to just move the
Is there any way to create partial backups in
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?Solution
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.
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.
Context
StackExchange Database Administrators Q#136650, answer score: 3
Revisions (0)
No revisions yet.