Move FILESTREAM database to new instance

To move filestream enabled database to new instance you can follow this process:

1. Identify files in your FILESTREAM DB with this script:

SELECT file_id, file_guid, type_desc, name,  physical_name, state_desc
FROM FSDataBaseName.sys.database_files
GO

 
filestreamdbinfo

 

2. Detach your database using Management Studio controls or using this query (On old SQL Server instance):

USE master
EXEC sp_detach_db FSDataBaseName
GO

3.(Not Neccesarry) – Now the database is detached, you can also move physical files of the database (.mdf, .ldf, BLOBs directory) to new locations

4. Enable FILESTREAM on new SQL Server instance. This msdn article will help you. Sometimes you also need to enable this via SQL Management Studio. Connect to new instance -> Right click on instance name -> Properties -> Advanced -> FILESTREAM access level -> Set to: Full Access enabled

5. Grant full permissions to BLOB storage folder for your new SQL Server instance. You need to grant permissions for account, which is running SQL instance. Usually it is
NT Service\MSSQL$SQLEXPRESS or NT Service\MSSQLSERVER.
(In Select Users or Groups dialog, you need to click LOCATIONS and select the root server name)

5. Attach the database on new Instance. You can use Management Studio controls or use this T-SQL script:

USE [master]
GO
CREATE DATABASE [FSDataBaseName] ON
( FILENAME = N'C:\FSDataBase\FSDataBaseName.mdf' ),
( FILENAME = N'C:\FSDataBase\FSDataBaseName_log.ldf' ),
FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT
( NAME = N'RBSFileStreamFile', FILENAME = N'C:\FSDataBase\BLOBFolder' )
FOR ATTACH
GO

 


Leave a Reply

Your email address will not be published. Required fields are marked *

About me

I am web developer, tech enthusiast and fitness junkie 🙂

Calendar

December 2024
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
3031