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.
Answer by Tom Staab ·
I finally had the time to research this, and I found (at least most of) my answer in 2 MSDN pages.
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.
Answer by SQLMaster ·
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.