Have you ever come across a situation where you need to attach a database using existing files (mdf, ldf & file stream data), but you don't have an option to mention the file stream folder when you use SSMS.
This won't be an issue if the filestream folder resides on its original path.
Let's look at an example. I have SQL data, log and filestream data which I will first attach using SSMS.
- Right-click on the database folder in SSMS and choose "Attach"
- Click the "Add" button and choose the data file (.mdf). Upon selecting the mdf file it will list the associated ldf file as well. But make sure to change the path accordingly from the second pane.
- Click on Ok.
Even if you try to generate the scripts using SSMS it doesn't provide you with the option to include the filestream folder.
USE [master] GO CREATE DATABASE [SampleSqlDatabase] ON ( FILENAME = N'D:\_SQL_DATA\SampleSqlDatabase_Data.mdf' ), ( FILENAME = N'D:\_SQL_LOG\SampleSqlDatabase_Log.ldf' ) FOR ATTACH GO
We can use the above code snippet and include the file stream path.
USE [master] GO CREATE DATABASE [SampleSqlDatabase] ON (FILENAME = N'D:\_SQL_DATA\SampleSqlDatabase_Data.mdf'), (FILENAME = N'D:\_SQL_LOG\SampleSqlDatabase_Log.ldf'), FILEGROUP NewFSGroup CONTAINS FILESTREAM ( NAME = 'NewDatabaseFileStream' ,FILENAME = 'D:\_SQL_DATA\SampleSqlDatabase_FSData_New' ) FOR ATTACH GO
Then you will be able to attach the database without an issue. You can use any name for the FILEGROUP and NAME (under the FILEGROUP). It doesn't need to be the same as your source database.
Hope this will be a helpful tip to you.
No comments:
New comments are not allowed.