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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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
|
1 2 3 4 5 6 7 8 |
USE SampleDB Go SELECT name AS "File Group Name" , type_desc AS "File Group Type" FROM sys.filegroups; Go |
Results:
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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.
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.
Fabulous, what a weblog it is! This website
presents useful information to us, keep it up.
It’s an amazing article designed for all the online visitors; they will obtain advantage from it
I am sure.
Foarte tare!
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!
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
Thank you for your comment – This website is hosted by Blacknight.com – http://tracking.blacknight.com/aff_c?offer_id=5&aff_id=732