How To Detach and Attach a Database Containing FileStream Data

You may want to move a database containing “File Stream” data to another disk with the detach attach method.

Moving a database containing “File Stream” data with the detach attach method is slightly different from the normal detach attach method.

In the article titled “How To Change The Disk Files On Database Files“, I explained the process of changing the database’s disks with the detach attach method.

In this article we will also do this for databases that contain file stream data.

Before reading this article, I recommend you read the article titled “What is File Stream in SQL Server“.

In some cases, there may not be space on the discs where the database files are located.

For this reason, you may need to transfer some or all of the database files to other disks.

There are several ways to do this. We will talk about the two most commonly used ways in this article.

Before you do detach, you need to save the result of the following script in order to save database file’s paths.

As shown in the following figure, right click on the database and select detach from the task tab to disconnect the database from the sql server first.

If we click on the Detach tab and there is a connection on the database, we need to select Drop Connections as below so that these connections are terminated before detach.

If detach is not performed again, you should run the above script by clicking on the Script and adding the following script to the top of the script created by SQL Server.

Before detach, you need to make sure that there are no requests for the application.

The best way to be sure of this is to discuss with application developers, ask them to stop the application, and then disable Login.

From the Security-> Logins tab of SQL Server Management Studio, right-click the login using the application and select Properties.

Then from the pop-up, we select Disabled from the Status tab as shown below and press OK.

After the detach process is finished, you need to move the database files using the copy paste method.

If we want to attach in normal ways after the move is finished, we will get an error like below.

Because when we want to Attach on SSMS, the FileStream data does not appear.

Msg 5120, Level 16, State 105, Line 3

Unable to open the physical file “C:\MSSQL\FileStreamVeri”. Operating system error 2: “2(The system can not find the specified file.)”.

Msg 5105, Level 16, State 14, Line 3

A file activation error occurred. The physical file name ‘C:\MSSQL\FileStreamVeri’ may be incorrect. Diagnose and correct additional errors, and retry the operation.

Msg 1813, Level 16, State 2, Line 3

Could not open new database ‘FileStreamDB’. CREATE DATABASE is aborted.

Therefore, we need to perform this operation with the help of the following script.

In our example, we moved the filestream data from the “C:\MSSQL” folder to the “C:\MSSQL\FileStreamUpdate” folder and later attached it.