What is File Stream On SQL Server

The File Stream structure keeps the BLOB objects (document, image, video, etc.) integrated with the database on the NTFS File system on the server.

Creates file stream files on the file system and makes these files part of the database.

Instead of keeping Blob objects in the NTFS file system in the file stream structure, you can also keep them in the database using the varbinary (MAX) data type in the table.

If your files are less than 1 MB in general, keeping them on the database will improve performance.

However, since BLOB data is large in size, it will make database administration difficult (Backup, DBCC CHECKDB, Restore, etc.).

If we hold BLOB objects on file system using File Stream, we can insert, update and delete with tsql as if it is in the database.

Also, when we have a full backup, we get the backup of the file stream.

To clarify what the file stream structure is, let’s create a database that uses FileStream.

First of all we need to enable File Stream over instance.

We open the SQL Server Configuration Manager and go to the SQL Server Services section and right click on the instance we want to enable the file stream and click properties.

On the screen that opens, we go to the FILESTREAM tab and select the Enable FILESTREAM for Transact-SQL access option.

Enable FILESTREAM for Transact-SQL access means that we allow file stream data to be accessed with transact sql.

Enable FILESTREAM for file The I / O access option does not change anything for FileStream.

When you enable this feature, you can access File Tables as a share via Windows.

We recommend reading the article named “What is File Table and Differences From File Stream“.

Allow remote clients access to FILESTREAM data option allows us to allow file stream data to be accessed from remote computers.

I do not recommend activating this option with security.

Then we open filestream via sp_configure with the following script.

For other operations that can be performed via sp_configure, you may want to read the article titled “sp_configure (Server-Level Configurations in SQL Server)“.

We can also do this by right-clicking the property on the instance and then selecting Transact-SQL access enabled from the FILESTREAM Access Level in the Advanced tab.

After this process is finished, we need to restart SQL Server via SQL Server Configuration Manager.

You can see other operations that can be done through Configuration Manager in the article named “SQL Server Configuration Manager Settings“.

Create a database that uses FileStream:

On the SSMS, under Instance, right-click on the Databases tab and select New Database … as follows.

First, let’s create a normal database that does not use FileStream as follows.

Creating a database may seem like a simple job, but if you create a database with default values for large systems then your head will be very painful.

For this reason, I recommend reading the article titled “How To Create a Database On SQL Server“.

After creating the database, we right-click on the database on SSMS and click Properties, and in the Filegroups tab we will give a name to the FileGroup by clicking Add Filegroup as you see below.

I gave the name FILESTREAMGROUP.

After creating a filegroup for File Stream, right click again on the database via SSMS and click properties.

Then we give a name to the Logical Name by clicking Add on the following screen. I gave the name “FileStreamVeri”.

We choose FILESTREAM Data from File Type.

In the Filegroups section, we choose the FILESTREAMGROUP filegroup we created earlier.

From the Path section, we select the disk and the folder where the File Stream data will be located and click OK.

In order to get the create script of the database that I created, you can right click on the database and select Script Database as-> Create to-> New query editor.

The create script of the database looks like this:

After creating the database, create a table with file stream data.

We create the table with the help of the following script.

If a column of type uniqueidentifier does not exist as ROWGUIDCOL and NOT NULL in the table, you will get an error like this when creating the table.

Msg 5505, Level 16, State 1, Line 1

A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property.

We can backup and restore a database containing FileStream data like normal backup restore operations.

To detach and attach a database containing FileStream data, you can use the article entitled “How To Detach and Attach a Database Containing FileStream Data“.