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.
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).
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
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.