Initial Full Backup required for Filegroup Backups?

Question

I'm assuming that an initial FULL BACKUP is required if I am taking FILEGROUP backups and would like this confirmed. Many examples show one at the start, but don't make note that it is explicitly required.

I have a database that is terabytes in size with 12 filegroups, 4 of which are active and contain 50% of the overall data.

If I could backup 8 out of 12 read only sets (plus the PRIMARY) in the initial backup, and then separately keep the other 4, it would be much faster to restore, than restoring all 12, and then needing to restore newer versions of the 4 active filegroups. Is this possible, or am I looking at a FULL BACKUP of the whole database, then separate FILEGROUP backups for the active?

Answer 1

I think what you're looking for is PARTIAL backups, which backup read/write filegroups (including PRIMARY) and any specified read-only filegroups. For your requirement, you could perform a PARTIAL backup of the 4 read/write filegroups plus PRIMARY and a File Backup of the 8 read-only filegroups.

Your file backups would not need to be performed again unless A) you change them to read/write, or B) your file backups become damaged. Beyond that, the file backups can be used for any restore going forward as needed.

Your PARTIAL backup of the PRIMARY and read-write filegroups can then be used as the source for PARTIAL restores or as a differential base for DIFFERENTIAL PARTIAL backups.

NOTE: The PRIMARY filegroup contains system tables and database metadata, including metadata about the state of other database files/filegroups, so you should always include this in your regular backup processes, it can't be backed up once and retained for future reference like the read-only filegroups. If you've designed your database correctly, you would ideally have little to no user data in the PRIMARY filegroup anyway, so its includion in PARTIAL backups shouldn't make much difference to performance or space consumption.

EDIT:

As per Peter's comment, I wanted to include a quote from the documentation:

Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary filegroup.

So with a PARTIAL backup, you're backing up all read/write filegroups, including PRIMARY, without exception. It is possible to include read-only files or filegroups as well, but all read/write are included in a PARTIAL backup automatically.

Answer 2

I'm assuming that an initial FULL BACKUP is required if I am taking FILEGROUP backups and would like this confirmed.

Answer

No, a initial FULL BACKUP is not required in order to take FILEGROUP backups.

The documentation states that

Under the full recovery model, a complete set of full file backups, together with enough log backups to span all the file backups, is the equivalent of a full database backup.

It also says

Restoring a database using just file and log backups can be complex. Therefore, if it is possible, it is a best practice to perform a full database backup and start the log backups before the first file backup.

Lab Test

Here's an example of a scenario where a FILEGROUP backup and LOG backups (without a FULL backup) are enough to restore a database file:

CREATE DATABASE MyDB
 ON  PRIMARY 
( NAME = N'MyDB', FILENAME = N'D:\MyDB.mdf'), 
 FILEGROUP [FILEGROUP_1] 
( NAME = N'File_1', FILENAME = N'D:\File_1.ndf')
 LOG ON 
( NAME = N'MyDB_log', FILENAME = N'D:\MyDB_log.ldf');

ALTER DATABASE [MyDB] SET RECOVERY FULL;

Now that we have a database, let's start the changes.

USE MyDB;

CREATE TABLE table_File_1(numbers int NOT NULL)
ON FILEGROUP_1;

INSERT INTO table_File_1(numbers)
VALUES (1), (2), (3);

BACKUP DATABASE MyDB 
FILEGROUP = 'FILEGROUP_1'
TO DISK = 'D:\backups\MyDB_FILEGROUP_1.bck';

BACKUP LOG MyDB
TO DISK = 'D:\backups\MyDB_LOG_1.trn';

DROP TABLE table_File_1;

USE master;

RESTORE DATABASE MyDB 
FILEGROUP = 'FILEGROUP_1'
FROM DISK = 'D:\backups\MyDB_FILEGROUP_1.bck'
WITH RECOVERY;

RESTORE LOG MyDB
FROM DISK = 'D:\backups\MyDB_LOG_1.trn'
WITH RECOVERY;

Notice that the restore isn't complete at this point for SQL Server requires that transaction logs are enough to bring the LSN up to the point where the database is (in this simulation only one file is being restored while the database was still online and the other files are being modified) and after restoring this backup you get the following message:

If you query sys.master_files, you'll see that File_1 is not online yet.

SELECT file_id, name, state_desc 
FROM sys.master_files
WHERE database_id = DB_ID('MyDB');

Here's the result of that query:

It means more log backups must be restored to bring this file online in a consistent state. In this scenario we can take a new log backup and restore it right after it was taken.

BACKUP LOG MyDB
TO DISK = 'D:\backups\MyDB_LOG_2.trn';

RESTORE LOG MyDB
FROM DISK = 'D:\backups\MyDB_LOG_2.trn'
WITH RECOVERY;

Now you're done. After restoring MyDB_LOG_2.trn you get this message:

And querying sys.master_files you can see that File_1 is now online

Remarks

• As the documentation stated and the example showed it's possible to have a backup strategy with FILEGROUP and LOG BACKUPS without a FULL BACKUP;
• Notice that restoring these backups didn't bring back the dropped table and the reason is that our backup plan wasn't enough to restore all the files in a manner that I could stop the restoring at the point the table still existed (so be careful when planning your backup strategy).