Can you do filegroup backups without a full backup?

Question

question

Tom Staab asked Feb 20, '10

Can you do filegroup backups without a full backup?

We have several very large databases (1-3 TB), and full backups are taking far too long and interfering with other processes.

All but one of these databases already use multiple filegroups, so someone suggested backing up each filegroup individually instead of doing a full backup.

My question is this: Can we still restore differential backups without any full database backup? In other words, is there any fundamental difference between restoring all filegroups for a database and restoring a full backup? One particular concern is that the size of the differential backup will continue to grow without a full backup. I am hoping it will only reflect differences in each filegroup since that filegroup was backed up.

If it matters, we are using SQL Server 2008.

Thanks.

backup restore filegroup differential-backup

10 |1200

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

Answer 1 · 2010-02-22T00:42:21Z

Tom Staab answered Feb 22, '10

I finally had the time to research this, and I found (at least most of) my answer in 2 MSDN pages.

Backup Overview (SQL Server)

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. So it appears the answer to my original question is definitely "yes." I have a follow-up question I'll post next.

10 |1200

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

Answer 2 · 2010-02-20T14:53:22Z

SQLMaster answered Feb 20, '10

Assuming your databases are in FULL recovery model, if the filegroup that is being restored is read/write, an unbroken chain of log backups must be applied after the last data or differential backup is restored. This brings the filegroup forward to the log records in the current active log records in the log file. The recovery point is typically near the end of log, but not necessarily.

If the filegroup that is being restored is read-only, usually applying log backups is unnecessary and is skipped. If the backup was taken after the file became read-only, that is the last backup to restore. Roll forward stops at the target point.

1

10 |1200

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

Tom Staab ♦ commented · Feb 21, 2010 at 11:35 AM

Thanks for the help. My question is more about the differential backups. Essentially, do differential backups still work without a full database backup? I reworded my question to focus more on that.

0 ·

question