locked
Filestream data 2008 to 2017 RRS feed

  • Question

  • I am migrating databases from SQL Server 2008 to 2017. I have used backup and restore for some of them with success. I have a couple that contain Filestream data. I am having trouble with these. When I attempt the restore on 2017 I get a 'FILESTEAM is disabled' message. When I create the database first with Filestream implemented and try to restore I get a message that backup being used does not match the database. I have also tried 'Restore Files and Filegroups' with similar problems.  
    Thursday, December 5, 2019 4:21 PM

Answers

All replies

  • … on 2017 I get a 'FILESTEAM is disabled' message. 
    Then you forgot to enable the FileStream feature during installation of SQL Server 2017; basically that's a server level feature. Start SSM = "SQL Server Configuration Manager" to enable the feature afterwards.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, December 5, 2019 5:08 PM
  • Hi iGBobH,

    You could enable the FILESTREAM in SQL Server Configuration Manager by right-clicking SQL Server Services -> Properties -> FILESTREAM tab. And then restart the SQL Server service.

    >> I get a message that backup being used does not match the database. I have also tried 'Restore Files and Filegroups' with similar problems. 

    Could you please share us the detailed error message which is located at Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG about your issue?

    In addition, here is an article which may help.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 6, 2019 2:34 AM
  • I believe that Filestream has been enabled on the new SQL Server....the FILESTREAM Properties of SQL Server Services in the SQL Server Configuration Manager are checked (enabled)

    ERRORLOG Text ----

    2019-12-04 12:25:42.88 spid53      Setting database option COMPATIBILITY_LEVEL to 140 for database 'CPLfiles'.

    2019-12-04 12:25:42.89 spid53      Setting database option ANSI_NULL_DEFAULT to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.89 spid53      Setting database option ANSI_NULLS to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.89 spid53      Setting database option ANSI_PADDING to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.89 spid53      Setting database option ANSI_WARNINGS to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.90 spid53      Setting database option ARITHABORT to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.90 spid53      Setting database option AUTO_CLOSE to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.90 spid53      Setting database option AUTO_SHRINK to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.90 spid53      Setting database option INCREMENTAL to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.90 spid53      Setting database option AUTO_CREATE_STATISTICS to ON for database 'CPLfiles'.

    2019-12-04 12:25:42.90 spid53      Setting database option AUTO_UPDATE_STATISTICS to ON for database 'CPLfiles'.
    2019-12-04 12:25:42.90 spid53      Setting database option CURSOR_CLOSE_ON_COMMIT to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.90 spid53      Setting database option CURSOR_DEFAULT to GLOBAL for database 'CPLfiles'.
    2019-12-04 12:25:42.90 spid53      Setting database option CONCAT_NULL_YIELDS_NULL to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.91 spid53      Setting database option NUMERIC_ROUNDABORT to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.91 spid53      Setting database option QUOTED_IDENTIFIER to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.91 spid53      Setting database option RECURSIVE_TRIGGERS to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.91 spid53      Setting database option DISABLE_BROKER to ON for database 'CPLfiles'.

    2019-12-04 12:25:42.91 spid53      Setting database option AUTO_UPDATE_STATISTICS_ASYNC to OFF for database 'CPLfiles'.
    2019-12-04 12:25:42.91 spid53      Setting database option DATE_CORRELATION_OPTIMIZATION to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.91 spid53      Setting database option PARAMETERIZATION to SIMPLE for database 'CPLfiles'.

    2019-12-04 12:25:42.92 spid53      Setting database option READ_COMMITTED_SNAPSHOT to OFF for database 'CPLfiles'.

    2019-12-04 12:25:42.92 spid53      Setting database option READ_WRITE to ON for database 'CPLfiles'.

    2019-12-04 12:25:42.92 spid53      Setting database option RECOVERY to FULL for database 'CPLfiles'.

    2019-12-04 12:25:42.92 spid53      Setting database option MULTI_USER to ON for database 'CPLfiles'.

    2019-12-04 12:25:42.92 spid53      Setting database option PAGE_VERIFY to CHECKSUM for database 'CPLfiles'.

    2019-12-04 12:25:42.92 spid53      Setting database option FILESTREAM NON_TRANSACTED_ACCESS to FULL for database 'CPLfiles'.

    2019-12-04 12:25:42.93 spid53      Setting database option target_recovery_time to 60 for database 'CPLfiles'.

    2019-12-04 12:25:42.93 spid53      Setting database option delayed_durability to disabled for database 'CPLfiles'.

    2019-12-04 12:26:13.79 spid56      The operating system returned the
    error '21(The device is not ready.)' while attempting 'GetDiskFreeSpace'
    on 'A:\'.

    2019-12-04 12:26:13.79 spid56      The operating system returned the
    error '21(The device is not ready.)' while attempting 'GetDiskFreeSpace'
    on 'I:\'.

    2019-12-04 12:34:01.92 spid56      The operating system returned the
    error '21(The device is not ready.)' while attempting 'GetDiskFreeSpace'
    on 'A:\'.

    2019-12-04 12:34:01.93 spid56      The operating system returned the
    error '21(The device is not ready.)' while attempting 'GetDiskFreeSpace'
    on 'I:\'.

    Friday, December 6, 2019 7:07 PM
  • Hi iGBobH,

    >>The operating system returned the error '21(The device is not ready.)' while attempting 'GetDiskFreeSpace'on 'A:\'.

    This issue could usually be attributed to the disk being unreadable or inaccessible, or any disk sector being unreadable.

    For more details, please refer to the following articles:

    http://www.sqldbaexperts.com/the-operating-system-returned-the-error-21the-device-is-not-ready-while-attempting-getdiskfreespace/?i=1

    https://dba.stackexchange.com/questions/165816/the-operating-system-returned-error-21-the-device-is-not-ready/165829

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by iGBobH Wednesday, December 11, 2019 6:01 PM
    Monday, December 9, 2019 9:26 AM
  • I disabled the A:\ (floppy) and I:\ (CDRom) drives as described in the first link provided above and was immediately rewarded with a valid restore of both of the databases utilizing Filestream. Of course the problem I was having had nothing to do with Filestreaming and that made it much harder to solve...Thanks for the info!
    Wednesday, December 11, 2019 6:06 PM