question

Tom Staab avatar image
Tom Staab asked

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.

backuprestorefilegroupdifferential-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.

Tom Staab avatar image
Tom Staab answered

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 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.

SQLMaster avatar image
SQLMaster answered

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 comment
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.

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 Likes 0 ·

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.