How to: Restoring SQL File stream Data
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).
SELECT SERVERPROPERTY('FileStreamShareName') as ShareName,
SERVERPROPERTY('FIleStreamConfiguredLevel') as ConfigLevel,
SERVERPROPERTY('FileStreamEffectiveLevel') as EffectiveLevel
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
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:
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
Looking at the file system, you can see that the data is identical there as well.
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.
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:
Now running net share you can see that I have 2 shares for the 2 instances of SQL each with their own filestream share.
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)
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:
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.
Lisa Gardner
@SQLGardner
Comments
- Anonymous
September 14, 2013
Learn something new today, thanks for sharing :)