Attach FILESTREAM-Enabled Database with multiple data files and filestream directories

Good morning everyone, here we are again on a Wednesday morning. Let’s get straight to the point. I had a request yesterday to restore a FILESTREAM-enabled database from a snapshot from 3 weeks ago, onto a separate server. The issues I ran into were that there was nothing on the web about attaching multi streamed FILESTREAM-enabled databases, so after some serious overthinking in the shower this morning I decided to experiment with sample scripts I found on the web.

Scenario:

We have a SQL 2008 R2 server with multiple FILESTREAM-enabled databases. The database data and log files reside on NetApp storage and we are using SnapManager for SQL to manage our backups of the databases. A call came in to get one of the databases restore from 3 weeks ago and attach it to a separate server as they were looking to extract some data.

Process to attach a multiple filestream-enabled database:

On the NetApp filer I found the volumes in question and created clones from the snapshots on the required date.
Once the clones were created, I attached the volumes to the second server and they were give the drive letters of P and Q, where on the original server they are E and F (data and logs respectively).

My dilemma:

I was tring to attach the database from the P drive but midway through it was throwing an error saying it could not connect to the E drive.  So after some reading on attaching FILESTREAM-enabled databases here, I was able to figure out what i needed to attach a filestream-enabled database with multiple data files and filestream directories.

Steps required to attach the FILESTREAM database:

Using the sample code below you need to gather some data before you start from the original database:

USE Master
Go
SELECT
DB_NAME(database_id)  AS "Database Name"
, type_desc             AS "File Type"
, name                  AS "Logical File Name"
, physical_name         AS "Physical File"
, state_desc            AS"State"
FROM
sys.master_files
WHERE
database_id = DB_ID('SampleDB');
Go

Results are as below

SampleDB    ROWS        PRIMARYFile1        E:\Data\MSSQL10_50\MSSQL\Data\PRIMARYFile1.mdf        ONLINE
SampleDB    LOG            LogFile1            F:\Log\MSSQL10_50\MSSQL\Log\LogFile1._ldf            ONLINE
SampleDB    ROWS        DEFAULTROWSFile01    E:\Data\MSSQL10_50\MSSQL\Data\DEFAULTROWSFile01.mdf    ONLINE
SampleDB    ROWS        DEFAULTROWSFile02    E:\Data\MSSQL10_50\MSSQL\Data\DEFAULTROWSFile02.mdf    ONLINE
SampleDB    ROWS        DEFAULTROWSFile03    E:\Data\MSSQL10_50\MSSQL\Data\DEFAULTROWSFile03.mdf    ONLINE
SampleDB    ROWS        Sample_01_File01    E:\Data\MSSQL10_50\MSSQL\Data\Sample_01_File01.mdf    ONLINE
SampleDB    ROWS        Sample_01_File02    E:\Data\MSSQL10_50\MSSQL\Data\Sample_01_File02.mdf    ONLINE
SampleDB    ROWS        Sample_02_File01    E:\Data\MSSQL10_50\MSSQL\Data\Samples_02_File01.mdf    ONLINE
SampleDB    ROWS        Sample_02_File02    E:\Data\MSSQL10_50\MSSQL\Data\Sample_02_File02.mdf    ONLINE
SampleDB    ROWS        Sample_03_File01    E:\Data\MSSQL10_50\MSSQL\Data\Samples_03_File01.mdf    ONLINE
SampleDB    ROWS        Sample_03_File02    E:\Data\MSSQL10_50\MSSQL\Data\Sample_03_File02.mdf    ONLINE
SampleDB    FILESTREAM    DEFAULTFSDir01        E:\Data\MSSQL10_50\MSSQL\Data\DEFAULTFSDir01        ONLINE
SampleDB    FILESTREAM    Sample_01_Dir01        E:\Data\MSSQL10_50\MSSQL\Data\SampleFS_01_Dir01        ONLINE
SampleDB    FILESTREAM    Sample_02_Dir01        E:\Data\MSSQL10_50\MSSQL\Data\SampleFS_02_Dir01        ONLINE
SampleDB    FILESTREAM    Samples_03_Dir01    E:\Data\MSSQL10_50\MSSQL\Data\SampleFS_03_Dir01        ONLINE

Now we need to get a little more information about the filestream

USE SampleDB
Go
SELECT
name       AS "File Group Name"
, type_desc  AS "File Group Type"
FROM
sys.filegroups;
Go

Results:

PRIMARY        ROWS_FILEGROUP
DEFAULTROWS    ROWS_FILEGROUP
DEFAULTFS     FILESTREAM_DATA_FILEGROUP
Sample_01     ROWS_FILEGROUP
Sample_02     ROWS_FILEGROUP
Sample_03     ROWS_FILEGROUP
SampleFS_01    FILESTREAM_DATA_FILEGROUP
SampleFS_02    FILESTREAM_DATA_FILEGROUP
SampleFS_03    FILESTREAM_DATA_FILEGROUP

Once we have this data we can proceed with writing the required SQL script on the second server. This is the juicy bit:

USE master
Go
CREATE DATABASE Recovered_SampleDB ON
PRIMARY
(NAME = 'PRIMARYFile1', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\PRIMARYFile1.mdf'),
(NAME = 'DEFAULTROWSFile01', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\DEFAULTROWSFile01.mdf'),
(NAME = 'DEFAULTROWSFile02', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\DEFAULTROWSFile02.mdf'),
(NAME = 'DEFAULTROWSFile03', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\DEFAULTROWSFile03.mdf'),
(NAME = 'Sample_01_File01', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\Sample_01_File01.mdf'),
(NAME = 'Sample_01_File02', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\Sample_01_File02.mdf'),
(NAME = 'Sample_02_File01', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\Sample_02_File01.mdf'),
(NAME = 'Sample_02_File02', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\Sample_02_File02.mdf'),
(NAME = 'Sample_03_File01', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\Sample_03_File01.mdf'),
(NAME = 'Sample_03_File02', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\Sample_03_File02.mdf'),
FILEGROUP DEFAULTFS CONTAINS FILESTREAM
(NAME = 'DEFAULTFSDir01', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\DEFAULTFSDir01'),
(NAME = 'SampleFS_01_Dir01', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\SampleFS_01_Dir01'),
(NAME = 'SampleFS_02_Dir01', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\SampleFS_02_Dir01'),
(NAME = 'SampleFS_03_Dir01', FILENAME = 'P:\Data\MSSQL10_50\MSSQL\Data\SampleFS_03_Dir01')
LOG ON
(NAME = 'LogFile1',  FILENAME = 'Q:\Log\MSSQL10_50\MSSQL\Log\LogFile1._ldf')
FOR ATTACH
Go

And that should be it in a nutshell. If you have any comments or queries regarding the above please feel free to contact me.

8 comments

  1. I will immediately take hold of your rss feed as
    I can’t find your email subscription link
    or e-newsletter service. Do you’ve any? Please permit me
    understand in order that I could subscribe. Thanks.

  2. I liкe the helpful info үoս provide іn youг articles.
    I’ll bookmark your blog аnd check again here regularly.
    I’m quіte sure I ѡill learn plenty ߋf new stuff rigɦt hеre!
    Best of luck foг the next!

  3. Nice blog here! Also yourr site loads uup fast!
    Whhat host aree you using? Can I get your affiliate link to our host?

    I wish myy website loaded up as fast as yours lol

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.