The main goal of this blog post is to showcase how you can discover the FILESTREAM assets in your SQL Server by querying the metadata. Some of those metadata queries may not be obvious so sharing this to make it easier
1. Create database with a FILESTREAM filegroup
USE [master]
GO
--create database with Filestream
CREATE DATABASE [FileStreamTest]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'FileStreamTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\FileStreamTest.mdf' , SIZE = 204800KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ),
FILEGROUP [FileStreamFG] CONTAINS FILESTREAM DEFAULT
( NAME = N'FileStreamTestFStream', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\FileStreamTestFStream' , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'FileStreamTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\FileStreamTest_log.ldf' , SIZE = 270336KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
2. Create a table that will store the binary documents
USE [FileStreamTest]
GO
CREATE TABLE FSTiffs(
Guid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
documentID INT NOT NULL ,
documentType VARCHAR(10) NOT NULL,
FileContent VARBINARY(MAX) FILESTREAM NOT NULL ,
dateinserted DATETIME)
3. Discover which database and files contain FILESTREAM
--which database and files use filestream
SELECT db_name(database_id) dbname, name as file_name, physical_name, type_desc, *
FROM sys.master_files
WHERE type_desc = 'FILESTREAM'
4. Switch to the database and discover which table(s) contain FILESTREAM data
--which tables in the database have filestream enabled
USE [FileStreamTest]
GO
SELECT * FROM sys.tables
WHERE filestream_data_space_id IS NOT NULL
5. Using your preferred picture creator, create two .TIFF files: Sample1.tiff and Sample2.tiff. I used Paint in Windows
6. Insert the first file into the FILESTREAM-based table
--insert a TIFF file
INSERT INTO FSTiffs (documentID, documentType, FileContent , dateinserted)
SELECT 101, '.tiff', *, GETDATE() FROM OPENROWSET(BULK N'C:\temp\Sample1.tiff', SINGLE_BLOB) rs
7. Check what the file looks like in the FILESTREAM folder via Windows Explorer. Re-run the query from step 3 to get the file location.
8. Now update the newly-inserted row by replacing it with a different TIFF file
--update a document
UPDATE FSTiffs SET FileContent = ( SELECT * FROM OPENROWSET(BULK N'C:\temp\Sample2.tiff', SINGLE_BLOB) AS rs)
WHERE documentID = 101
9. Return data from the table
--select data from filestream table
SELECT * FROM FSTiffs
10. Delete the row
--delete a document
DELETE FSTiffs
WHERE documentID = 101
Enjoy!