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:

Results are as below

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

Results:

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

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

7 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 *