In this article, we'll explore a less used feature of SQL Server: filegroup restore (know also as piecemeal restore or partial restore), feature that has an interesting consequence because database can become inconsistent without any warning and standard checks will not reveal any issue.
Filegroup restore - demo
We'll start my creating a new database having the filegroup [PRIMARY] plus two new filegroups: [SECONDARY01] and [SECONDARY02]:
CREATE DATABASE FGRestore ON PRIMARY (NAME = N'FGRestore_PR', FILENAME = N'C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_PR.mdf'), FILEGROUP [SECONDARY01] (NAME = N'FGRestore_S01', FILENAME = N'C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_S01.ndf'), FILEGROUP [SECONDARY02] (NAME = N'FGRestore_S02', FILENAME = N'C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_S02.ndf') LOG ON (NAME = N'FGRestore_log', FILENAME = N'C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_log.ldf') GO ALTER DATABASE FGRestore SET RECOVERY FULL GO
Next, we'll create two tables: dbo.TablePK on SECONDARY01 filegroup (having ID column as PRIMARY KEY) and dbo.TableFK on SECONDARY02 filegroup (having ID column as FOREIGN KEY referencing dbo.TablePK(ID) and no primary key) and then we'll insert few rows in both tables ( 1 & 2 into dbo.TablePK and also 1 into dbo.TableFK):
CREATE TABLE dbo.TablePK(
ID INT NOT NULL PRIMARY KEY
) ON [SECONDARY01]
INSERT dbo.TablePK VALUES (1), (2)
GO
CREATE TABLE dbo.TableFK(
ID INT NOT NULL REFERENCES dbo.TablePK(ID)) ON [SECONDARY02]
INSERT dbo.TableFK VALUES (1)
GO
After these steps, the status of current database is:
Let's consider this moment to be T and let's create a full backup:
BACKUP DATABASE FGRestore TO DISK = 'FGRestore_FULL.bak' WITH INIT, FORMAT
Now, we'll insert few more rows into these table( 3 into dbo.TablePK and 2 & 3 into dbo.TableFK):
INSERT dbo.TablePK VALUES (3) INSERT dbo.TableFK VALUES (2), (3)
Let's consider this moment to be T+1 and let's create a log backup thus:
BACKUP LOG FGRestore TO DISK = 'FGRestore_LOG_01.trn' WITH INIT, FORMAT, NORECOVERY
Note: if we avoid this step, the next step (RESTORE DATABASE ... WITH PARTIAL, RECOVERY) should be executed using WITH REPLACE option (RESTORE DATABASE ... WITH REPLACE, PARTIAL, RECOVERY).
Because, we have used BACKUP LOG ... WITH NORECOVERY the status of the database changed to Restoring. Also the status of all data (type_desc = ROWS) files become Restoring:
SELECT name, physical_name, type_desc, state_desc FROM sys.master_files mf WHERE mf.database_id = DB_ID('FGRestore')
name physical_name type_desc state_desc ------------- ------------------------------------------------------ --------- ---------- FGRestore_PR C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_PR.mdf ROWS RESTORING FGRestore_log C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_log.ldf LOG ONLINE FGRestore_S01 C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_S01.ndf ROWS RESTORING FGRestore_S02 C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_S02.ndf ROWS RESTORING
the next step is to restore only the filegroup SECONDARY01 (used by dbo.TablePK) from a full database backup (moment T) thus:
RESTORE DATABASE FGRestore FILEGROUP = 'SECONDARY01' FROM DISK = 'FGRestore_FULL.bak' WITH PARTIAL, RECOVERY
This command will restore SECONDARY01 filegroup but also wil restore - by default - the PRIMARY filegroup. Because we have used WITH RECOVERY, SQL Server will bring online these filegroups and their associated files. Now, the status of every database file is as follow:
name physical_name state_desc ------------- ------------------------------------------------------ ---------------- FGRestore_PR C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_PR.mdf ONLINE FGRestore_log C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_log.ldf ONLINE FGRestore_S01 C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_S01.ndf ONLINE FGRestore_S02 C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_S02.ndf RECOVERY_PENDING
and because there is one more filegroup having non-ONLINE status (SECONDARY02 - FGRstore_S02) we'll bring it online with following RESTORE command:
RESTORE DATABASE FGRestore FILEGROUP = 'SECONDARY02' WITH RECOVERY
After this final step, all database files are online:
name physical_name type_desc state_desc ------------- ------------------------------------------------------ --------- ---------- FGRestore_PR C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_PR.mdf ROWS ONLINE FGRestore_log C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_log.ldf LOG ONLINE FGRestore_S01 C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_S01.ndf ROWS ONLINE FGRestore_S02 C:\DB\SQL\MSSQL13.SQL2016\MSSQL\DATA\FGRestore_S02.ndf ROWS ONLINE
and the status of both tables is
Now it comes the most interesting part:
Both RESTORE commands were executed successfully.
All files are ONLINE and we are able to read data from both tables.
Filegroup SECONDARY01 (dbo.TablePK) was restored at moment T but filegroup SECONDARY02 (dbo.TableFK) is still at moment T+1.
Because of FG restore, dbo.TablePK contains only two rows (1 & 2) while dbo.TableFK contains all rows (1, 2 & 3). In this this, we can see that referential integrity (foreign key constraint) is in the air because the values 3 from dbo.TableFK (table with foreign key) is missing from parent table (dbo.TableFK). The end result is database inconsistency.
SELECT *, 'TablePK' AS TableName FROM dbo.TablePK SELECT *, 'TableFK' AS TableName FROM dbo.TableFK GO
ID TableName ----------- --------- 1 TablePK 2 TablePK (2 row(s) affected) ID TableName ----------- --------- 1 TableFK 2 TableFK 3 TableFK --> This foreign key values doesn't have an associated value within primary key (dbo.TablePK.ID) (3 row(s) affected)
If we are checking current database with DBCC CHECKDB everything seems to be fine:
DBCC CHECKDB() WITH ALL_ERRORMSGS, NO_INFOMSGS /* Command(s) completed successfully. */
The only way to detect this database inconsistency is to use with following command:
DBCC CHECKCONSTRAINTS() /* Table Constraint Where ---------------- ---------------------------- --------- [dbo].[TableFK] [FK__TableFK__ID__24927208] [ID] = '3' DBCC execution completed. If DBCC printed error messages, contact your system administrator. */
Another interesting aspect is that foreign key constraint remains enabled and trusted after this filegroup restore:
SELECT name, is_not_trusted, is_disabled FROM sys.foreign_keys x /* name is_not_trusted is_disabled ------------------------- -------------- ----------- FK__TableFK__ID__24927208 0 0 */
Final note
After restoring filegroups is recommended to check constraints in order to find possible database inconsistency generated by missing values (rows) from primary key. This feature is available only in Enterprise and Developer edition.
