question

Tom Staab avatar image
Tom Staab asked

Should I use a multibase differential backup or separate ones for each filegroup?

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:

  1. (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?
  2. Should we mark filegroups read-only once they are over a week old?
  3. If we mark older filegroups read-only, should we use compression? (see this article)
sql-server-2008filegroupcompressiondifferential-backup
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Rob Farley avatar image
Rob Farley answered
  1. Sure - I think it's a good candidate too.

  2. You should definitely mark filegroups as read-only if they really are. It means that querying on them won't require locks, so things will just work faster.

  3. Using compression is an interesting. Data compression in SQL Server only compresses the database when it tries to write more information to the page, so you'd have to rebuild all the data in the filegroup to see the benefits. The answer to whether this is worthwhile is "It depends". You'd have to try to weigh up the benefit of having the data squeezed into less space. It may mean more data can fit into the server's RAM, but if you'd rather your RAM was full of current, writable data, then it might not impact you so much.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.