How to Backup and Restore a SQL Server FILESTREAM Enabled Database

By:   |   Comments (14)   |   Related: > FILESTREAM


Problem

Most SQL Server DBAs have questions about backing up and restoring FILESTREAM enabled databases. In this tip, we will take a look at the steps Database Administrators need to follow in order to perform a backup and restore of a FILESTREAM database.  This tip includes a general explanation of the FILESTREAM technology introduced with SQL Server 2008.  This is followed by examples and scripts to setup the backup and recovery process in your environment.

Solution

In SQL Server 2008 one can store BLOBs (e.g. Images, Video, Word, Excel, PDF, MP3, etc files) in the NT file system rather than in a database file. This can be achieved by using the new FILESTREAM feature which was introduced in SQL Server 2008.  However, the big question in the mind of many DBA is how FILESTREAM enabled databases can be backed up and restored.   Are there any differences from a typical database?  In this tip, we will go through an example of how to backup and restore a FILESTREAM enabled database.

Creating a FILESTREAM Enabled Database

Let us start by creating a FILESTREAM enabled database namely FileStreamDB by executing the TSQL code below.

Use Master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'FileStreamDB')
DROP DATABASE FileStreamDB
GO

USE master
GO

CREATE DATABASE [FileStreamDB] ON PRIMARY
( NAME = N'FileStreamDB', FILENAME = N'D:\FileStreamDB\FileStreamDB.mdf',
SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10% )
LOG ON
( NAME = N'FileStreamDB_log', FILENAME = N'D:\FileStreamDB\FileStreamDB_log.ldf' ,
SIZE = 10MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)
GO

ALTER DATABASE [FileStreamDB]
ADD FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM
GO

ALTER DATABASE [FileStreamDB]
ADD FILE (NAME = N'FileStreamDB_FSData', FILENAME = N'D:\FileStreamDB\FileStreamData')
TO FILEGROUP FileStreamGroup
GO

 

Creating a table with FILESTREAM columns

Let us now create the FileStreamDataStorage table by executing the TSQL code below.  This table will be used to store FILESTREAM data:

Use FileStreamDB
GO
IF EXISTS (SELECT name FROM sys.all_objects WHERE name = N'FileStreamDataStorage')
DROP TABLE FileStreamDataStorage
GO

CREATE TABLE [FileStreamDataStorage]
(
[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 FileStreamGroup
GO

To store a BLOB using FILESTREAM feature, you must have a column of datatype VARBINARY (MAX) along with the FILESTREAM attribute. In addition to this the table must also have a UNIQUEIDENTIFIER column with the ROWGUIDCOL attribute.

Inserting FILESTREAM Data

Let us now add a row to FileStreamDataStorage table by execute the below mentioned TSQL code.

Use FileStreamDB
GO
INSERT INTO [FileStreamDataStorage] (FileStreamData)
SELECT * FROM
OPENROWSET(BULK N'C:\SampleFiles\Image1.JPG' ,SINGLE_BLOB) AS Document
GO

-- Execute the below mentioned TSQL code to retrieve the data from
-- FileStreamDataStorage table.

USE FileStreamDB
GO
SELECT ID
, CAST([FileStreamData] AS VARCHAR) as [FileStreamData]
, FileStreamDataGUID
, [DateTime]
FROM [FileStreamDataStorage]
GO

image001

For more information about inserting, updating or deleting FILESTREAM data, check out - Creating a SQL Server 2008 FILESTREAM Enabled Database and Using INSERT, UPDATE and DELETE statements to manage FILESTREAM Data.

 

Backup FILESTREAM Enabled Database

A DBA can perform a full backup of a FileStreamDB database by executing the T-SQL Code below.  In this tip, all of the backups are using the database backup compression feature which was introduced in SQL Server 2008.

/* Perform a Full Backup of FileStreamDB */
Use master
GO
BACKUP DATABASE FileStreamDB
TO DISK =N'C:\DBBackup\FileStreamDB.BAK'
WITH COMPRESSION
GO

/* Perform a Tail Log Backup of FileStreamDB */
BACKUP LOG FileStreamDB
TO DISK =N'C:\DBBackup\FileStreamDB.TRN'
WITH COMPRESSION, NORECOVERY
GO

 

image002 

Once the database backups have successfully completed, the next step will be to go ahead and restore the FileStreamDB database.

Restore FILESTREAM Enabled Database

Let us now go ahead and restore the Full backup of the FileStreamDB database with the NORECOVERY option.

/* Identify the list of the database & log files contained within the backup set */
Use master
GO
RESTORE FILELISTONLY
FROM DISK = N'C:\DBBackup\FileStreamDB.BAK'
GO
/* Restore the Full Backup with NORECOVERY option */
RESTORE DATABASE FileStreamDB
FROM DISK = N'C:\DBBackup\FileStreamDB.BAK'
WITH NORECOVERY, FILE =1
GO

 

Next, you need to restore the transaction log backup of the FileStreamDB database with the RECOVERY option to complete the restoration process.

/*Restore Tail Log Backup with RECOVERY option */
RESTORE DATABASE FileStreamDB
FROM DISK = N'C:\DBBackup\FileStreamDB.TRN'
WITH RECOVERY, FILE =1
GO

 

image003 

Once the database restoration has successfully completed you can execute the T-SQL code below to check whether the earlier inserted record is still available.

USE FileStreamDB
GO
SELECT ID
, CAST([FileStreamData] AS VARCHAR) as [FileStreamData]
, FileStreamDataGUID
, [DateTime]
FROM [FileStreamDataStorage]
GO

 

image004 

You will are able retrieve the same record values from FileStreamDataStorage table as the database was restored successfully.

Restore FILESTREAM Enabled Database Using MOVE Files Option

A DBA can execute the T-SQL code below to identify the physical name of files currently used by FileStreamDB database.

SELECT name AS [File Name]
, physical_name AS [Physical Name]
, type_desc AS [File Type]
, state_desc AS [Database Status]
FROM FileStreamDB.sys.database_files
GO

 

image005

Note: Prior to executing the T-SQL code below, delete the earlier backup files and perform a new Full and Transaction Log backup of the FileStreamDB database.

The T-SQL code below will restore the FileStreamDB database using the MOVE option.

Use master
GO
RESTORE FILELISTONLY
FROM DISK = N'C:\DBBackup\FileStreamDB.BAK'
WITH FILE =1
GO
/* Restore Full Backup with MOVE & NORECOVERY */
RESTORE DATABASE FileStreamDB
FROM DISK = N'C:\DBBackup\FileStreamDB.BAK'
WITH
MOVE 'FileStreamDB' to 'E:\FileStreamDB\FileStreamDB.mdf',
MOVE 'FileStreamDB_log' to 'E:\FileStreamDB\FileStreamDB_log.ldf',
MOVE 'FileStreamDB_FSData' to 'E:\FileStreamDB\FileStreamData',
NORECOVERY, FILE =1
GO
/* Restore Tail Log Backup with RECOVERY */
RESTORE DATABASE FileStreamDB
FROM DISK = N'C:\DBBackup\FileStreamDB.TRN'
WITH RECOVERY, FILE =1
GO

 

image006

Once the FileStreamDB database is restored successfully, a DBA can execute the T-SQL code below to identify the physical name of files currently used by FileStreamDB database.

SELECT name AS [File Name]
, physical_name AS [Physical Name]
, type_desc AS [File Type]
, state_desc AS [Database Status]
FROM FileStreamDB.sys.database_files
GO

 

image007

You could see in the above snippet that the all the database files are now available in E:\FileStreamDB location after the successful restoration.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Tuesday, October 16, 2018 - 2:16:53 AM - Kal Youssef Back To Top (77956)

Dear Ashish

Thank you for the post

I wanted to know if Filestreaming is supported using incremental backups as our EMC backup software has an issue with this type of backup

please advise

regards

Kal

Tuesday, January 23, 2018 - 7:14:14 AM - Murali Back To Top (75019)
    1.  My Database in Simple recovery model. then how to restore the Filestream enabled Database to New Server?

 

Many thanks!

Murali 

 

 

Saturday, October 1, 2016 - 9:39:11 AM - nathanayyanar Back To Top (43472)

Hi Ashish,

One of the user database with filestream enabled which is around 68GB in size.

And when the same database is taking 7 hours time to complete the backup.Backup location is in n/w share drive.

Is there any way to speed up the backup using any option  like MAXtransfer.

 

Thanks in advance

 

Wednesday, January 7, 2015 - 9:37:47 AM - sudhir Back To Top (35855)

Saif, 

 

I think one way is to do backup by filegroup and do piecemeal filegroups restore. I think, piecemeal restore is an enterprise feature only. your filestream data tables will not be available untill you restore that filegroup. 

 

HTH!!!

Saturday, August 23, 2014 - 2:46:46 AM - saif Back To Top (34240)

Hi,

Can I restore the content Database with filestream to a database without filestream. I have read that I can do so, but I don't know how to do so.

Yhose articles also say that the blobs will be migrated into the database with this kind of redtore, which means I won't loose the data.

Please share the steps.

Regards

Thursday, May 22, 2014 - 10:18:26 AM - Pratik Back To Top (30888)

Can we give path of different server as filestream path to store BLOB?

Tuesday, March 19, 2013 - 5:50:35 PM - Julio Back To Top (22881)

can restore different or multiple files *. bak from microsoft magnament studio, from a path example c: \ bakups \; containing different databases. many scrips in internet but none refers to this

Tuesday, October 27, 2009 - 5:16:18 AM - Claire.Brooking Back To Top (4310)
Hi there, I’m part of the SQL Backup team at Red Gate Software, and I spotted the MSSQLTips team’s reply, inviting vendors to give tips on how to cut down backup and restore time. Returning to the first question too, our backup tool, SQL Backup, also supports the backing up and restoring of databases using the FILESTREAM data type. Re. cutting down backup and restore time, we launched SQL Backup 6 this year with a new compression level that’s designed to significantly reduce the size of the backup file taken. Our in-house testing shows that by using one of the four compression levels available in SQL Backup Pro, database backups can be compressed by up to 95%. The smaller the backup, the more time you’ll save on the restore.  You can see a DBA's review of SQL Backup 6 here: http://www.simple-talk.com/sql/sql-tools/sql-backup-6-wind-of-change/. Shawn, the author, also talks about network resilience for backups and ‘self-healing’ log shipping in SQL Backup Pro (features that can save you time and effort if you’re transferring or writing backups across a flaky network) among other advanced backup and restore options, such as kill existing connections and list orphaned users.  

 

Hope this helps!

 

Claire Brooking

SQL Backup brand manager

Red Gate Software

Saturday, October 24, 2009 - 3:28:05 AM - Ashish Kumar Mehta Back To Top (4296)

Generally backup and restore of FILESTREAM enabled database that too which has large number of FILESTREAM files will be slower than a backup or restore of an SQL Server database of an equivalent size. This happens because of the extra overhead of backing up each NTFS file and this overhead becomes move noticeable when the FILESTREAM files are very small in size (less than 1 MB).

May I know out of 320 GB, how much space is currently used for FILESTREAM data storage? And also let me know how many files are stored and there average size?

I am assuming that you have millions of FILESTREAM files and there could be many files which are of size below 1 MB. Ideally FILESTREAM should be used when you are storing file greater than 1 MB.

You can optimize NTFS performance by turning off the below two processes by running it within the command prompt.

FSUTIL BEHAVIOR SET DISABLE8DOT3 1

The above option disables the generation of 8.3 names on all NTFS volumes on the server where FILESTREAM Feature is configured.

FSUTIL BEHAVIOR SET DISABLELASTACCESS 1

The above option will turn off updating the last access time for a file when it is accessed.

Hope this helps!

Friday, October 23, 2009 - 6:21:06 PM - bretthawton Back To Top (4293)

Hi There,

Idera's SQL Safe V6.0 (currently in late stage beta, GA on 2 November 2009) handles both backup and restore of databases including FileStream data beautifully.

The product, with its new compression algorithms gleaned directly from world renowned compression experts and patented IntelliCompress technology, will compress SQL Server database backups such as these to a very high ratio indeed (typically 85%+) whilst at the same time dramatically reducing both backup and restore times.

The beta can be accessed until 30 October here http://www.idera.com/Promo/SQL-safe-6-SQL-vdb-Beta/

Brett Hawton

Idera Product Designer 

  

Friday, October 23, 2009 - 10:00:30 AM - admin Back To Top (4289)

Prashant,

We will pose the question to some SQL Server vendors in the community to see if they can be of any assistance to reduce the backup and recovery time.

Thank you,
The MSSQLTips Team

Friday, October 23, 2009 - 7:34:31 AM - prashantgh Back To Top (4284)

hi,

It is a very nice post. i have tried taking the backup of database containing filestream and it works fine. I have tried restoring it on other server also and that works too.

Now, i am facing another problem. Our database is big in size (approx. 320 GB) and it takes time to backup and restore it. Therefore, the client wants us to suggest some technique to reduce the time.

I have tried piecemeal restore which allows you to backup and restore individual filegroups in the database. It works fine with all the other filegroups except for Filestream ones. I am able to take backup and not able to restore it.

Do u have any idea??

Regards,

Prashant.

Saturday, October 17, 2009 - 3:52:52 AM - Ashish Kumar Mehta Back To Top (4214)

If you are restoring the FULL backup of a FILESTREAM enabled database on a different server, you will be able to see all the images files along with other content within the database once the database is successfully restored on the new machine. However, prior to the restore please make sure that the FILESTREAM feature is enabled on “Server B”.

If there are frequent changes happening on the FILESTEAM enabled database then it would be a good practice to execute CHECKPOINT prior to the backup of database as this will EXPLICIT call FILESTREAM Garbage Collector process.  

Hope this helps!

Thursday, October 15, 2009 - 7:16:04 AM - david.wendelken Back To Top (4196)

 The article didn't prove to me that everything was backed up.  (But it's early in the am, so maybe my thinking cap isn't on just right...)

When the backup was restored on the same machine, the image files would still be in their original places, so it could just be finding the files it needs by accident.

If the database is restored on a different, stand-alone machine (so images on a shared network drive wouldn't be found), what happens?  Are the images restored also?  Do the various directories they are placed in get created in the file system?  How does that work if the source machine was installed on D: and the restored machine used C:?  Or are they images just not included in the backup?

If the files are just not included, how does a dba know what directories and files need to be backed up?  What strategies/tools can they use to easily get all the files backed up in a manner that makes it easy to restore them to all the crazy, uncontrolled places a developer could have loaded them from?  Or are images limited to a few special directories that the dba creates?

Thanks!















get free sql tips
agree to terms