I will start by referencing an important MSDN article I found:
Working with Multibase Differential Backups
For those of you who don't already know, a multibase differential backup is a single differential database backup whose base is multiple file or filegroup backups.
excerpt from the article:
When a Multibase Differential Might Be Useful
In rare situations, a multibase differential may make sense. For example, a database administrator might use a full database differential with a full set of file backups. A whole set of file backups replaces a single database backup, creating a multibase differential. Depending on database usage, the differential might be fairly small. In any case, restoring a file follows the same procedure: Restore the file backup first and then the full database differential.
In this case, a multibase differential is easier to manage than several separate differential file backups; and the backup and restore steps remain simple. There is no confusion over which backups to restore, because all files are treated the same.
If you use multiple bases for differential backups, keep the multibase strategy as simple as possible. Avoid strategies that use complex multibase differentials, especially ones that require that some files or filegroups be treated differently from other files or filegroups at restore time. For example, avoid intermixing full database backups, file backups, and database differentials. Also, avoid grouping files differently in the full database backup and the differential backups, unless the groupings are very simple and predictable, as in the previous example.
In our case, we have a 3 TB database with 40 filegroups. We create a separate filegroup for each day. The large majority of writes is always on the current day with some occasional edits going back no more than a week. Therefore, we always have 33 of 40 filegoups that are essentially (but not technically) read-only.
Given that architecture, I have 3 questions:
- (the title question) Should I use a multibase differential backup or separate ones for each filegroup? Based on my understanding of the MSDN article, I think this might actually be a good case for a multibase differential backup. What do you think?
- Should we mark filegroups read-only once they are over a week old?
- If we mark older filegroups read-only, should we use compression? (see this article)
