Datafly.pro

Créons donc une petite base de données et sauvegardons là pour examiner les solutions :

CREATE DATABASE [filestr] ON  PRIMARY
( NAME = N'filestr', FILENAME = N'E:\Temp\filestr.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP FilestrGR CONTAINS FILESTREAM
(NAME= 'FilestrBlob', Filename = 'E:\Temp\FILESTRBLOB')
 LOG ON
( NAME = N'filestr_log', FILENAME = N'E:\Temp\filestr_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
USE filestr
CREATE TABLE mytablewithfilestream
(ID int,
Theblob varbinary(max) FILESTREAM,
GUID_ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE)
INSERT INTO mytablewithfilestream
VALUES
(1,CAST('Here are datas' AS varbinary(max)), NEWID())
GO
USE master
BACKUP DATABASE filestr TO DISK = 'E:\Temp\filestr.bak' WITH INIT

Première solution : restauration partielle

Nous allons restaurer uniquement le filegroup qui contient les données de ligne :

RESTORE DATABASE [filestr]
FILEGROUP = 'PRIMARY'
FROM  DISK = N'/var/opt/mssql/data/backup/filestr.bak'
WITH MOVE N'filestr' TO N'/var/opt/mssql/data/filestr.mdf',  
MOVE N'filestr_log' TO N'/var/opt/mssql/data/filestr_log.ldf',
RECOVERY

Cette solution fonctionne, mais ne règle qu'à moitié le problème, à savoir :

• La colonne filestream est inacessible (logique !). Si on tente un SELECT * : Msg 670, Large object (LOB) data for table "dbo.mytablewithfilestream" resides on an offline filegroup ("FilestrGR") that cannot be accessed.
• On peut toutefois lire les autres colonnes de la table si cette colonne n'est pas dans la clause SELECT
• Il n'est plus possible en l'état de modifier la table pour en retirer sa colonne filestream

Deuxième solution : retirer le filestream avant la sauvegarde

Pour cela nous allons devoir, avant de sauvegarder la base, supprimer la colonne de type filestream. Avant cela, nous pouvons copier son contenu dans une nouvelle colonne, si son contenu le permet : une colonne varbinary(MAX) ne peut pas contenir plus de 2 Go...

USE filestr 
ALTER TABLE [dbo].[mytablewithfilestream] ADD [TheBlob2] VARBINARY(MAX) 
GO 
UPDATE [dbo].[mytablewithfilestream] SET TheBlob2 = TheBlob 
GO 

Puis nous allons retirer la colonne filestream et renommer la colonne varbinary.

ALTER TABLE [dbo].[mytablewithfilestream] DROP COLUMN [TheBlob]
GO
EXEC sp_rename 'dbo.mytablewithfilestream.TheBlob2','TheBlob','Column'
GO

Et enfin, un peu de nettoyage (avec une série d'opérations) s'impose pour pouvoir retirer le filestream :

• Retirer l'attribut FILESTREAM de la table
• Vider le fichier filestream avec un DBBC SHRINKFILE
• Faire passer le garbage collector pour nettoyer le filestream. Pour que cela fonctionne, il faudra passer la base de données en recovery SIMPLE
• Optionnellement, on pourra aussi retirer la colonne GUID et sa contrainte unique.
• Puis, je pourrai retirer le fichier et le groupe de fichiers filestream

-- Nettoyage du filestream
ALTER TABLE [dbo].[mytablewithfilestream] SET (FILESTREAM_ON="NULL")
GO
DBCC SHRINKFILE ('FileStrBlob',EMPTYFILE)
GO
ALTER DATABASE filestr SET RECOVERY SIMPLE
GO
EXEC sp_filestream_force_garbage_collection @dbname = 'Filestr', @filename = 'FileStrBlob'
CHECKPOINT
GO

-- optionnel, je peux aussi retirer la colonne GUID
ALTER TABLE [dbo].[mytablewithfilestream] DROP CONSTRAINT [UQ__mytablew__D47F3BCBF9147496]
ALTER TABLE [dbo].[mytablewithfilestream] DROP COLUMN [GUID_ID]

-- retirer le fichier et groupe de fichier
ALTER DATABASE [filestr]  REMOVE FILE [FilestrBlob]
ALTER DATABASE [filestr]  REMOVE FILEGROUP [FilestrGR]

Il ne me reste plus qu'à sauvegarder (et restaurer sous linux) ma base qui n'a plus de filestream...

Et pour le in-memory ?

Une petite note, et un bémol concernant les filegroup in memory : si vous avez une base avec un filegroup in memory (CONTAINS MEMORY_OPTIMIZED_DATA), vous ne pourrez le supprimer que si aucun objet in memory n'a jamais été créé. Mais heureusement SQL Server sous linux supporte le in-memory...

La documentation Microsoft est claire sur ce point : "Une fois que vous utilisez un groupe de fichiers à mémoire optimisée, vous ne pouvez le supprimer qu’en supprimant la base de données."
Et ils ajoutent (ce qui est un peu abusif) : "Dans un environnement de production, il est peu probable que vous deviez supprimer le groupe de fichiers mémoire optimisé."