How to Detach and Attach a SQL Server FILESTREAM Enabled Database


By:   |   Updated: 2009-11-11   |   Comments (7)   |   Related: More > FILESTREAM

Problem
Most SQL Server DBAs have questions about how to detach and attach a FILESTREAM enabled databases. In this tip, we will take a look at the steps Database Administrators need to follow in order to detach and attach a FILESTREAM database once Data, Log and FILESTREAM container files have been moved from the default location to a new location. This tip includes a general explanation of the FILESTREAM technology introduced with SQL Server 2008. This is followed by examples and scripts to detach and attach FILESTREAM enabled database 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 detached and attached once Data, Log and FILESTREAM container files are moved from the default location to a new location. Are there any differences from a typical database? In this tip, we will go through an example of how to detach and attach a FILESTREAM enabled database.


Creating a FILESTREAM Enabled Database

Let us start by creating a FILESTREAM enabled database named FileStreamDB by executing the T-SQL 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 T-SQL 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 T-SQL code.

Use FileStreamDB
GO
INSERT INTO [FileStreamDataStorage] (FileStreamData)
SELECT * FROM 
OPENROWSET(BULK N'C:\SampleFiles\Image1.BMP' ,SINGLE_BLOB) AS Document
GO
INSERT INTO [FileStreamDataStorage] (FileStreamData)
SELECT * FROM 
OPENROWSET(BULK N'C:\SampleFiles\Image2.BMP' ,SINGLE_BLOB) AS Document
GO
INSERT INTO [FileStreamDataStorage] (FileStreamData)
SELECT * FROM 
OPENROWSET(BULK N'C:\SampleFiles\Image3.BMP' ,SINGLE_BLOB) AS Document
GO
INSERT INTO [FileStreamDataStorage] (FileStreamData)
SELECT * FROM 
OPENROWSET(BULK N'C:\SampleFiles\Image4.BMP' ,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

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, INIT
GO

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

For more information about How to Backup and Restore a SQL Server FILESTREAM Enabled Database, check out - How to Backup and Restore a SQL Server FILESTREAM Enabled Database.


Identify all the FILESTREAM Database Files

You can get the list of all the files which are related to FileStreamDB by executing the below mentioned T-SQL code.

/*
Identify all the Files which are related to FileStreamDB 
*/
SELECT 
file_id AS FileID, 
file_guid AS FileGUID, 
type_desc AS FileType,
name AS Name, 
physical_name AS PhysicalName, 
state_desc AS State
FROM FileStreamDB.sys.database_files
GO

In the above snippet you could see that FileStreamDB, FileStreamDB_Log and FileStreamDB_FSData files are located in D:\FileStreamDB.


Detach FileStreamDB Enabled Database

Let us now go ahead and detach the FileStreamDB database by executing the below mentioned T-SQL code.

USE [master]
GO
ALTER DATABASE [FileStreamDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'FileStreamDB'
GO

Once the above TSQL code has executed successfully the FileStreamDB will be detached from your SQL Server 2008 instance.


Attach FileStreamDB Enabled Database

Let us now go ahead and attach the FileStreamDB database by executing the below mentioned T-SQL code. Here, you can see in the below code that I have mentioned the data and log file still SQL Server was able to attach the FileStreamDB to the SQL Server instance without any issues. However, things are much different if you plan to detach the database and then move the FileStreamDB, FileStreamDB_Log and FileStreamDB_FSData files from the default D:\FileStreamDB to any other location. We will see how to attach the FILESTREAM enabled database in such a scenario later down in this tip.

USE [master]
GO
CREATE DATABASE [FileStreamDB] ON 
( FILENAME = N'D:\FileStreamDB\FileStreamDB.mdf' ),
( FILENAME = N'D:\FileStreamDB\FileStreamDB_log.ldf' )
FOR ATTACH
GO

As FileStreamDB, FileStreamDB_Log and FileStreamDB_FSData files were still present in the D:\FileStreamDB default location, you will be able to attach the database successfully without any issues.

Next, let us go ahead and detach the FileStreamDB database once again and then move the FileStreamDB folder from the D: drive to the C: drive. Once the move has successfully completed, you will be able to see all the three files in C:\FileStreamDB location.

1. FileStreamDB.mdf
2. FileStreamDB_log.ldf
3. FileStreamData (Folder)

Now, if you go ahead and attach the FileStreamDB database using the same script as above, except changing the file locations rom D: to C:,  you will end up seeing the below error.

USE [master]
GO
CREATE DATABASE [FileStreamDB] ON 
( FILENAME = N'C:\FileStreamDB\FileStreamDB.mdf' ),
( FILENAME = N'C:\FileStreamDB\FileStreamDB_log.ldf' )
FOR ATTACH
GO
Msg 5120, Level 16, State 105, Line 1
Unable to open the physical file "D:\FileStreamDB\FileStreamData". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5105, Level 16, State 14, Line 1
A file activation error occurred. The physical file name 'D:\FileStreamDB\FileStreamData' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'FileStreamDB'. CREATE DATABASE is aborted.
 

The reason you get this error is because the database thinks the FILESTREAM data is still in the same location.  Keep reading for how to solve this error.


Attaching a FILESTREAM database when FILESTREAM container is moved from the default location

Since I have copied all the Data, Log and FILESTREAM container from the default D:\FileStreamDB location to C:\FileStreamDB location; I was unable to attach the FILESTREAM enabled database. To overcome from this scenario, you need to mention the location of the FILESTREAM container within the attach database script as shown below.

USE [master]
GO
CREATE DATABASE [FileStreamDB] ON 
( FILENAME = N'C:\FileStreamDB\FileStreamDB.mdf' ),
( FILENAME = N'C:\FileStreamDB\FileStreamDB_log.ldf' ),
FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT 
( NAME = N'FileStreamDB_FSData', FILENAME = N'C:\FileStreamDB\FileStreamData' )
FOR ATTACH
GO

However, you won't be able attach the FILESTREAM enabled database if the FILESTREAM container location is changed when you try to attach the database using SQL Server Management Studio. In such a scenario you should be using the above T-SQL code to attach the FILESTREAM enabled database.

Once the above script has executed successfully you can verify the location of all the FILESTREAM database files by executing the below mentioned T-SQL code.

/*
Identify all the Files which are related to FileStreamDB 
*/
SELECT 
file_id AS FileID, 
file_guid AS FileGUID, 
type_desc AS FileType,
name AS Name, 
physical_name AS PhysicalName, 
state_desc AS State
FROM FileStreamDB.sys.database_files
GO

You can see in the above snippet that all the database files are now available in C:\FileStreamDB location.

 

Next Steps



Last Updated: 2009-11-11


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, December 11, 2015 - 4:40:01 AM - Just Me Back To Top

 

USE [master]

GO

CREATE DATABASE [FileStreamDB] ON 

( FILENAME = N'G:\Databases\Docman.mdf' ),

( FILENAME = N'G:\Databases\Docman_log.ldf' ),

FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT 

( NAME = N'FileStreamDB_FSData', FILENAME = N'G:\DocutracksRepository\DbFileStorage\FileStreamData' )

FOR ATTACH

GO

 

This returns an "Unable to open the physical file "G:\DocutracksRepository\DbFileStorage\FileStreamData". Operating system error 5: "5(Access is denied.)"." on my machine.

I have given rights to Everyone on the folder and run SSMS as admin and still nothing...

What the hack...


Sunday, March 01, 2015 - 12:12:09 AM - Amit Kumar Back To Top

 

Thanks Ashish for the great info about filestream data file attach and detach processes.

 

Isn't its better to take the full backup and then restore with move, by specifying data,log and filestream file locations. 

 

RESTORE DATABASE [cgp_psms_91_backup] FROM  DISK = N'U:\DBA\CGP_PSMS-Full Database Backup_91.bak' WITH  FILE = 1,  

MOVE N'CGA_CPSMS_Prim' TO N'T:\SQL_DATA\CGP_PSMS_91_backup.mdf',  

   MOVE N'CGP_PSMS_log' TO N'T:\SQL_DATA\CGP_PSMS_91_backup_12.ldf', 

     MOVE N'FileStream_1' TO N'V:\SQL_Data\CGP_PSMS_91_backup_14.ndf',  NOUNLOAD,  STATS = 10

GO

 

Sunday, January 05, 2014 - 5:58:59 AM - Mistik Back To Top
USE [master]
GO
CREATE DATABASE [FileStreamDB] ON 
( FILENAME = N'C:\FileStreamDB\FileStreamDB.mdf' ),
( FILENAME = N'C:\FileStreamDB\FileStreamDB_log.ldf' ),
FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT 
( NAME = N'FileStreamDB_FSData', FILENAME = N'C:\FileStreamDB\FileStreamData' )
FOR ATTACH
GO

 

 

Thank you for the code and screen shots in this tip.  It is very helpful!

Thank you,


Thursday, July 25, 2013 - 7:26:18 AM - Tomek Back To Top

Mike - for me it works in SQL Server 2012. Do you have FILESTREAM feature enabled?


Wednesday, July 03, 2013 - 4:09:34 PM - Mike Back To Top

This works great in SQL Server 2008 R2, however it does not work in SQL Server 2012. Would you have any idea why? I just get the same error message as if I was trying to attach without the FILEGROUP clause. Thanks.


Monday, June 04, 2012 - 2:56:51 AM - Ali Motamed Back To Top

thank you.

i live in iran

and not access to best sql server document 

bye.


Wednesday, November 11, 2009 - 1:02:27 PM - admin Back To Top

Ashish,

Thank you for the code and screen shots in this tip.  It is very helpful!

Thank you,
The MSSQLTips Team



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools