Återställning av filgrupper med tabeller som har Foreign Keys till tabeller i andra filgrupper

Ett mycket  bra sätt att ta reda på saker är att testa, dessutom kanske man lär sig något nytt på kuppen, så vi gör ett litet test helt enkelt.

Vi börjar med att skapa en ny databas med lämpligt namn:
CREATE DATABASE SQLService
GO

Vi lägger till två nya filgrupper:
USE [master]
GO
ALTER DATABASE [SQLService] ADD FILEGROUP [FG1]
GO
ALTER DATABASE [SQLService] ADD FILEGROUP [FG2]
GO

Vi skapar en ny fil på resp ny filgrupp:
USE [master]
GO
ALTER DATABASE [SQLService] ADD FILE ( NAME = N’SQLService2′, FILENAME = N’D:\Databaser\SQL2014\SQLService2.ndf’ , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG1]
GO
ALTER DATABASE [SQLService] ADD FILE ( NAME = N’SQLService3′, FILENAME = N’D:\Databaser\SQL2014\SQLService3.ndf’ , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG2]
GO

Då är det dags att skapa tabeller på de nya filgrupperna med forign keys som refererar mellan filgrupperna.
Tabell test2 på filgrupp FG2 har FK som refererar till tabell test på filgrupp FG1
Tabell test4 på filgrupp FG1 har FK som refererar till tabell test3 på filgrupp FG2

Vi skapar tabell test på filgrupp FG1:
CREATE TABLE test
(id int identity(1,1) primary key not null,
col varchar(200)
)
ON FG1
GO

Vi skapar tabell test2 med FK till tabell test på filgrupp FG2:
CREATE TABLE test2
(id int,
col varchar(200),
foreign key (id) references test(id)
)
ON FG2
GO

Vi skapar tabell test3 på filgrupp FG2:
CREATE TABLE test3
(id int identity(1,1) primary key not null,
col varchar(200)
)
ON FG2
GO

Vi skapar tabell test4 med FK till tabell test3 filgrupp FG1:
CREATE TABLE test4
(id int,
col varchar(200),
foreign key (id) references test3(id)
)
ON FG1
GO

Vi fyller på lite testdata:
INSERT INTO test VALUES(‘Rad 1 tabell test FG1’)
INSERT INTO test VALUES(‘Rad 2 tabell test FG1’)
INSERT INTO test3 VALUES(‘Rad 1 tabell test3 FG2’)
INSERT INTO test3 VALUES(‘Rad 2 tabell test3 FG2’)
INSERT INTO test2 VALUES(1, ‘FK Rad 1 tabell test2 FG2’)
INSERT INTO test2 VALUES(2, ‘FK Rad 2 tabell test2 FG2’)
INSERT INTO test4 VALUES(1, ‘FK Rad 1 tabell test4 FG1’)
INSERT INTO test4 VALUES(2, ‘FK Rad 2 tabell test4 FG1’)

Vi tar en full backup av databasen:
BACKUP DATABASE SQLService TO DISK = ‘D:\backup\sqlservice.bak’
GO

Nu tar vi bort databasen:
DROP DATABASE SQLService
GO

Dags att återställa databasen
Vi gör restore på filgrupp FG1 där tabellerna test och test3 ligger:
RESTORE DATABASE SQLService FILEGROUP=‘Primary’ FROM disk = ‘D:\Backup\sqlservice.bak’
WITH PARTIAL, NORECOVERY
RESTORE DATABASE SQLService FILEGROUP=‘FG1’ FROM disk = ‘D:\Backup\sqlservice.bak’
WITH RECOVERY
GO

Vi har nu endast återställt filgrupp FG1, dags att kontrollera vad vi kommer åt:
USE SQLService
GO
SELECT * FROM test
Datat åtkomligt precis som förväntat.

SELECT * FROM test2
Vi får följande felmeddelande:
Msg 8653, Level 16, State 1, Line 96
The query processor is unable to produce a plan for the table or view ‘test2’ because the table resides in a filegroup that is not online.
Vi har ju faktiskt inte återställt filgrupp FG2 så det är inte helt oväntat.

SELECT * FROM test3
Samma felmeddelande som ovan då denna tabell också ligger på filgrupp FG2 som just nu inte är online.

SELECT * FROM test4
Datat åtkomligt precis som tidigare

Vi provar att uppdatera data i tabell test4
UPDATE test4 SET col = ‘testdata’
WHERE id = 1

Det gick bra
Vi provar att lägga till en ny rad i tabell test4
INSERT INTO test4 VALUES(3, ‘FK Rad 3 tabell test4 FG1’)

Det gick inte, samma felmeddelande som tidigare:
Msg 8653, Level 16, State 1, Line 111
The query processor is unable to produce a plan for the table or view ‘test3’ because the table resides in a filegroup that is not online.

Detta var väl ganska väntat eftersom SQL Server vid insert av nytt data måste göra en kontroll i tabellen som Foreign Keyn refererar till, i vårt fall tabell test3 som just nu är offline

 Slutligen:
Av det här testet kan vi dra slutsatsen att även om vi har en tabell med foreign keys till tabeller som inte är online kan vi komma åt datat bara filgruppen där tabellen ligger är online.
För att tex göra restore snabbare kan man med fördel fördela datat på olika filgrupper för att på så sätt slippa återställa allt.