SQL Server FileStream

Sqlserer when installing, remember to check FileStream;

Start below:

Perform:

EXEC sp_configure filestream_access_level,2
RECONFIGURE

To restart the database service:

To create a database:

CREATE DATABASE [Filestream]

Because FileStream is a special-purpose filegroup, to create a filegroup specify Constains FileStream

ALTER DATABASE Filestream
ADD FILEGROUP fg_ft CONTAINS Filestream

When adding a filegroup, the physical file path does not need to be manually created, the manual creation will be error, I have encountered;

ALTER DATABASE [Filestream]
ADD FILE
(
Name=n ' Filestream1 ',
Filename=n ' F:\Filestream '
) to FILEGROUP fg_ft

Result: (Automatically created)

FILESTREAM.HDR is a FileStream system file, and Filesream container contains FileStream header information; do not delete

Create a containing varbinary (max) field and specify the FileStream property, and also the uniqueidentifier type and specify the ROWGUIDCOL property;

CREATE TABLE Filestre
(
ID uniqueidentifier ROWGUIDCOL not NULL UNIQUE,
NAME SYSNAME,
BLOB VARBINARY (MAX) FILESTREAM NULL
)

Insert data The first article is a text document, the second is a picture;

INSERT INTO Filestre
SELECT NEWID (), ' name1 ', * FROM OPENROWSET (BULK N ' F:\COUNTERS. TXT ', Single_blob) as FILE1
INSERT INTO Filestre
SELECT NEWID (), ' name2 ', * FROM OPENROWSET (BULK N ' F:\20160927.png ', Single_blob) as FILE1

The physical file path is automatically created, we insert two files, this file we can modify the file format, and can open look;

Below we test restore backup, very strange, also just solve my doubts above, I took the file group file path deleted, restore the file path can be created;

Backup:

BACKUP DATABASE [Filestream] to Disk=n ' F:\FILE_D.BAK '

BACKUP LOG [Filestream] to Disk=n ' F:\FILE_L.BAK '

Delete the database FileStream, delete the file path, the data files are not;

Restore the database below:

RESTORE DATABASE [Filestream] from disk=n ' F:\FILE_D.BAK ' with NORECOVERY
RESTORE LOG [Filestream] from disk=n ' F:\FILE_L.BAK '

All data is identical to the database before deletion;

SQL Server FileStream