Przywróć bazę danych z wyłączeniem danych FILESTREAM

Kontekst
Opracowujemy system z dużą bazą danych na dole. Jest to baza danych MS SQL działająca na SQL Server 2008 R2. Całkowity rozmiar bazy danych wynosi około 12 GB.

Spośród nich około 8,5 GB znajduje się w jednej tabeli BinaryContent. Jak sama nazwa wskazuje, jest to tabela, w której przechowujemy proste pliki dowolnego rodzaju bezpośrednio w tabeli jako BLOB. Ostatnio testowaliśmy możliwość przeniesienia wszystkich tych plików z bazy danych do systemu plików za pomocą FILESTREAM.

Dokonaliśmy niezbędnych modyfikacji naszej bazy danych bez żadnych problemów, a nasz system nadal działa dobrze po migracji. BinaryContentTabela wygląda mniej więcej tak:

CREATE TABLE [dbo].[BinaryContent](
    [BinaryContentID] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [varchar](50) NOT NULL,
    [BinaryContentRowGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL
) ON [PRIMARY] FILESTREAM_ON [FileStreamContentFG]
ALTER TABLE [dbo].[BinaryContent] ADD [FileContentBinary] [varbinary](max) FILESTREAM  NULL
ALTER TABLE [dbo].[BinaryContent] ADD  CONSTRAINT [DFBinaryContentRowGUID]  DEFAULT (newsequentialid()) FOR [BinaryContentRowGUID]

Wszystko znajduje się w PRIMARYgrupie plików, z wyjątkiem pola FileBinaryContentznajdującego się w osobnej grupie plików FileStreamContentFG.

Scenariusz
Z punktu widzenia dewelopera często chcielibyśmy mieć świeżą kopię bazy danych z naszego środowiska produkcyjnego, aby móc pracować z najnowszymi danymi. W takich przypadkach rzadko jesteśmy zainteresowani plikami przechowywanymi w BinaryContent (teraz za pomocą FILESTREAM).

To działa prawie tak, jak byśmy tego chcieli. Wykonujemy kopię zapasową bazy danych, bez takiego strumienia plików:

BACKUP DATABASE FileStreamDB
FILEGROUP = 'PRIMARY' 
TO DISK = 'c:\backup\FileStreamDB_WithoutFS.bak' WITH INIT

I przywróć go w następujący sposób:

RESTORE DATABASE FileStreamDB
FROM DISK = 'c:\backup\FileStreamDB_WithoutFS.bak'

Wygląda na to, że działa OK, a nasz system działa tak długo, jak omijamy części wykorzystujące FileBinaryContentpole. Możemy na przykład uruchomić następujące zapytanie bez problemu:

SELECT TOP 10 [BinaryContentID],[FileName],[BinaryContentRowGUID]
--,[FileContentBinary]
FROM [dbo].[BinaryContent]

Oczywiście, jeśli nie skomentuję powyższej linii, w tym FileContentBinaryw zapytaniu, otrzymuję błąd:

Dane dużych obiektów (LOB) dla tabeli „dbo.BinaryContent” znajdują się w grupie plików offline („FileStreamContentFG”), do której nie można uzyskać dostępu.

Nasze uchwyty plików systemowych, w których zawartość jest ustawiony na null, więc co ja lubię robić coś takiego:

UPDATE [dbo].[BinaryContent]
SET [FileContentBinary] = null

Ale to oczywiście daje mi taki sam błąd jak powyżej. W tym momencie utknąłem.

Pytanie
Czy istnieje sposób na przywrócenie bazy danych bez konieczności przywracania wszystkiego z FileStreamContentFGgrupy plików? Albo aktualizując wartości do null, gdy próbuję powyżej, lub domyślnie do null, gdy brakuje pliku lub coś?

A może podchodzę do problemu w niewłaściwy sposób?

Jestem programistą z natury i nie mam zbyt dużej wiedzy jako DBA, więc przepraszam, jeśli pomijam tutaj jakąś trywialną rzecz.

To, co próbujesz zrobić, pozostawiłoby bazę danych w niespójnym (transakcyjnie) stanie, dlatego nie jest to możliwe.

Częściowa Database Dostępność whitepaper jest przydatny podręcznik i zawiera przykładowe, jak sprawdzić, czy dana tabela lub plik jest online. Jeśli dostęp do danych odbywa się za pomocą procedur przechowywanych, można stosunkowo łatwo włączyć tę kontrolę.

Jednym z alternatywnych (ale nieco hackerskich) podejść, które warto sprawdzić w twoim scenariuszu, byłoby ukrycie stołu i zastąpienie go widokiem.

-- NB: SQLCMD script
:ON ERROR EXIT
:setvar DatabaseName "TestRename"
:setvar FilePath "D:\MSSQL\I3\Data\"

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SET NOCOUNT ON;
GO

USE master;
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'$(DatabaseName)')
  DROP DATABASE $(DatabaseName)
GO

CREATE DATABASE $(DatabaseName) 
ON PRIMARY 
  (
  NAME = N' $(DatabaseName)'
  , FILENAME = N'$(FilePath)$(DatabaseName).mdf'
  , SIZE = 5MB
  , MAXSIZE = UNLIMITED
  , FILEGROWTH = 1MB
  ) 
, FILEGROUP [FG1] DEFAULT
  ( 
  NAME = N' $(DatabaseName)_FG1_File1'
  , FILENAME = N'$(FilePath)$(DatabaseName)_FG1_File1.ndf'
  , SIZE = 1MB
  , MAXSIZE = UNLIMITED
  , FILEGROWTH = 1MB 
  ) 
, FILEGROUP [FG2] CONTAINS FILESTREAM
  ( 
  NAME = N'$(DatabaseName)_FG2'
  , FILENAME = N'$(FilePath)Filestream'
  )
LOG ON 
  ( 
  NAME = N'$(DatabaseName)_log'
  , FILENAME = N'$(FilePath)$(DatabaseName)_log.ldf'
  , SIZE = 1MB
  , MAXSIZE = UNLIMITED
  , FILEGROWTH = 1MB
  )
GO

USE $(DatabaseName);
GO

CREATE TABLE [dbo].[BinaryContent](
    [BinaryContentID] [int] IDENTITY(1,1) NOT NULL
    , [FileName] [varchar](50) NOT NULL
    , [BinaryContentRowGUID] [uniqueidentifier] ROWGUIDCOL UNIQUE DEFAULT (NEWSEQUENTIALID()) NOT NULL
  , [FileContentBinary] VARBINARY(max) FILESTREAM  NULL
) ON [PRIMARY] FILESTREAM_ON [FG2]
GO 

-- Insert test rows
INSERT
  dbo.BinaryContent
  (
  [FileName]
  , [FileContentBinary]
  )
VALUES
  (
  CAST(NEWID() AS VARCHAR(36))
  , CAST(REPLICATE(NEWID(), 100) AS VARBINARY)
  );
GO 100

USE master;
GO

-- Take FILESTREAM filegroup offline
ALTER DATABASE $(DatabaseName)
MODIFY FILE (NAME = '$(DatabaseName)_FG2', OFFLINE)
GO

USE $(DatabaseName);
GO

-- Rename table to make way for view
EXEC sp_rename 'dbo.BinaryContent', 'BinaryContentTable', 'OBJECT';
GO

-- Create view to return content from table but with NULL FileContentBinary
CREATE VIEW dbo.BinaryContent
AS

SELECT
  [BinaryContentID]
    , [FileName] 
    , [BinaryContentRowGUID]
  , [FileContentBinary] = NULL
FROM
  [dbo].[BinaryContentTable];
GO

-- Check results as expected
SELECT TOP 10
  *
FROM
  dbo.BinaryContent;
GO

Możesz wyizolować tabelę za pomocą FILESTREAModdzielnej bazy danych i utworzyć odwołanie do niej w PRODUCTIONbazie danych za pomocą widoku.

To pozwoli ci robić, co chcesz, bez uciekania się do hacków.