What are the challenges you face when working across database platforms? Take the survey
Options

Cloning a SQL Server database with a FILESTREAM

JoppeJoppe Posts: 1 New member

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?

Tagged:

Answers

  • Options
    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!

    Sean Quigley | Product Support Engineer | Redgate Software

    Have you visited our Help Center?





Sign In or Register to comment.