Cloning a SQL Server database with a FILESTREAM

Joppe

We have a database with a FILESTREAM. For a FILESTREAM the DIRECTORY_NAME property must be unique within an SQL instance. If we try to clone an image on the same SQL instance as the original database it will fail due to conflicting DIRECTORY_NAME. We can always temporarily change the DIRECTORY_NAME of the original database, clone it based on its image, change the DIRECTORY_NAME of the clone to something unique and finally restore the DIRECTORY_NAME of the original database.

However, according to Microsoft documentation, the DIRECTORY_NAME is used when forming the full path to a file. Hence that would require us to also change the name of the FILESTREAM folder on the Windows server used by the clone. Is that possible?

squigley

Good Afternoon,
Thanks for your inquiry with SQL Clone.
We would recommend the following method:

1. Move the filetable to a different database within the same instance.
2. Run the below query

 
ALTER DATABASE SilverSuite
SET FILESTREAM ( NON_TRANSACTED_ACCESS = OFF , DIRECTORY_NAME = NULL
 
If you get an error message, try setting the NON_TRANSACTED_ACCESS to FULL in the user interface (DB properties/options) and then execute the ALTER DATABASE command again.
 
Once this is done, please check if the image can be created.

Thanks!