How can we help you today? How can we help you today?

Cloning a SQL Server database with a FILESTREAM

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?

Joppe
0

Comments

1 comment

Sort by
  • 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!
    squigley
    0

Add comment

Please sign in to leave a comment.