It is not uncommon to have multiple copies of the same database on a single instance or to restore copy of a database from one instance to another for testing.  I was asked - “How does this work when I have filestream data in my database?”  The thought process is pretty much the same, but you do need to take into consideration where the filestream data will go. The filestream files are backed up and restored along with the SQL backup.  Just think of the main folder for the filestream data as another database file. I will walk through some testing that I did in order to give you a better understanding of this process.   

To validate that Filestream is enabled and set for remote access, we can leverage the following (remote access is not required for this demo but required for additional testing I am doing).    

 

How to enable Filestream

SELECT SERVERPROPERTY('FileStreamShareName') as ShareName,    

SERVERPROPERTY('FIleStreamConfiguredLevel') as ConfigLevel,    

SERVERPROPERTY('FileStreamEffectiveLevel') as EffectiveLevel      

 clip_image002    

 

You can also see the share for the instance with the net share command from the command prompt. MSSQLSERVER is the default value for a default instance of SQL    

clip_image004    

I then created a database, a table with filestream data, and entered a few rows.    

--create a database    

--Ensure you update the path to your folders for data and Filestream folders

--Note: The filestream folder C:\FSData\FSTest must exist prior to running below.

 

CREATE DATABASE [FSTest] ON  PRIMARY

( NAME = N'FSTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FSTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512000KB ),

 FILEGROUP [FSFiles] CONTAINS FILESTREAM  DEFAULT

( NAME = N'FSFiles', FILENAME = N'C:\FSData\FSTest\FSFiles' )

 LOG ON

( NAME = N'FSTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FSTest_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 512000KB )

GO

 

USE FSTest

GO

 

CREATE TABLE [FileStreamData]

(

[ID] [INT] IDENTITY(1,1) NOT NULL,

[FileStreamData] VARBINARY(MAX) FILESTREAM NULL,

[FileStreamDataGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),

[DateTime] DATETIME DEFAULT GETDATE()

)

ON [PRIMARY]

FILESTREAM_ON  FSFiles

GO

 

--no file created on filesystem

INSERT INTO [FileStreamData] (FileStreamData, FileStreamDataGUID, DateTime)

VALUES (NULL,NEWID (), GETDATE());

 

--insert a 0 length record - obtains a file handle

INSERT INTO [FileStreamData] (FileStreamData, FileStreamDataGUID, DateTime)

VALUES (CAST ('' as varbinary(max)),NEWID (), GETDATE());

 

--inserts a record and creates a file with some data in it

INSERT INTO [FileStreamData] (FileStreamData, FileStreamDataGUID, DateTime)

VALUES (CAST ('This is test data' as varbinary(max)),NEWID (), GETDATE());

-- Execute the below mentioned TSQL code to retrieve the data from

-- FileStreamDataStorage table.

SELECT ID, CAST([FileStreamData] AS VARCHAR) as [FileStreamData]

, FileStreamDataGUID, [DateTime]

, FileStreamData.PathName (0) as FilePath

FROM [FileStreamData]

GO

You can see the results here:    

 

clip_image005

 

I then backed up the database using the GUI and restored the database to a new database on the same instance with a new name (FSTest2). Note that I put the mdf and ldf files in the same location as the other database, but I created a separate folder for the filestream data:

 

RESTORE DATABASE [FSTest2]

--Backup the DB with GUI - then restore - note filestream new path

--Ensure you update the path to your folders for backup, data and Filestream folders

--Note: The filestream folder C:\FSData\FSTest2 must exist prior to running below.

RESTORE DATABASE [FSTest2]

FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\FSTest.bak'

WITH  FILE = 1, 

MOVE N'FSTest' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FSTest2.mdf', 

MOVE N'FSTest_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FSTest2_1.ldf', 

MOVE N'FSFiles' TO N'C:\FSData\FSTest2\FSFiles',  NOUNLOAD,  REPLACE,  STATS = 10

GO

 

 

The data is identical from the below script:

 

SELECT ID, CAST([FileStreamData] AS VARCHAR) as [FileStreamData]

, FileStreamDataGUID, [DateTime]

, FileStreamData.PathName (0) as FilePath

FROM FSTest..[FileStreamData]

GO

 SELECT ID, CAST([FileStreamData] AS VARCHAR) as [FileStreamData]

, FileStreamDataGUID, [DateTime]

, FileStreamData.PathName (0) as FilePath

FROM FSTest2..[FileStreamData]

GO

 

clip_image007

Looking at the file system, you can see that the data is identical there as well.    

clip_image009

 

You dig deeper into the folder structure and you can see the 2 identical files are there. NOTE: There are 2 files but 3 rows of data in the table….why? If you recall, the first record inserted had a NULL value for the filestream data therefore there is no file. This is often done when you want a placeholder for a file you will import later.

 

clip_image011

 

Now let’s add another instance in to the mix.  For time savings, I used a SQL named instance that I already had on this VM.  The instance name is SSL. I configured it for Filestream which you can see below:

 

clip_image013

 

Now running net share you can see that I have 2 shares for the 2 instances of SQL each with their own filestream share.

 

clip_image015

   

I restored the same database backup but restored it as FSTest3.  I also named the directory FSTest3.  You can now see all 3 have the same structure (and all 3 have the same files)    

clip_image017

 

You can also see that the data in the db in the second instance is the same as the data in the other databases:

 SELECT ID, CAST([FileStreamData] AS VARCHAR) as [FileStreamData]    

, FileStreamDataGUID, [DateTime]

, FileStreamData.PathName (0) as FilePath

FROM [FileStreamData]

GO

You can see the results here:    

clip_image018    

A few key takeaways from this post:  

1.      Filestream files are in fact backed up and restored along with your database. Therefore you database backup file may seem quite large compared to the mdf files.

2.      The filestream share name is at the instance level while the local file system path to the directory is set at the database level.  All filestream databases on an instance will use the same share name yet each database will have its own folder structure.

3.      Create a standard for your filestream data folder structures.  Consider if you will have multiple instances and/or databases on the same server as well as multiple servers with filestream data.  You may want to derive a standard such as instancename\databasename to make it easy to know where the filestream data resides and identify to which database it belongs.

 

Download Script File

 

Lisa Gardner 

@SQLGardner