SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Filegroup Restoring and Database Consistency

By Bogdan SAHLEAN,

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. 

 
Total article views: 721 | Views in the last 30 days: 6
 
Related Articles
FORUM

Restoring database - Filegroups

Restoring database - Filegroups

FORUM

Filegroup Restore

Database Restoring using Filegroup Backup

FORUM

Backup FileGroup and Restore on Secondary Database

Backup FileGroup and Restore on Secondary Database with same filegroups

FORUM

FileGroups

FileGroups RESTORE

ARTICLE

Filegroup Restoring and Join Elimination

This article is just a short follow up of previous article on filegroup restoring that describes one...

Tags
filegroup    
restore    
sql server    
 
Contribute