none
Database in recovery pending status

    Question

  • I am having problems with a database since I upgrated to windows 10 creators update; the database use filestream.

    First I have problems restoring the database, getting the error 5591. When it seems that I could solve the problem with the solution on this post: in this post


    But today, when I start the computer again, and open sql server management studio, I can't access to the database because it is in recovery pending status. This, if I use a normal account user.

    If I open management studio as an administrator account, I get the same error 5591, that filestream is not enabled.

    I have also tried to unistall all sql server 2016 applications and reinstall all, but the problem is the same, when I reboot the computer, I get this error, that the database is in recovery pending status.

    Thanks.

    Saturday, April 08, 2017 9:01 AM

Answers

  • Try to change the service account of the SQL Server process. In my case it helped to set it to "Local System" (as a workaround). Seems to be some rights issue.
    • Marked as answer by ComptonAlvaro Wednesday, April 12, 2017 9:12 AM
    Wednesday, April 12, 2017 8:53 AM

All replies

  • Hello,

    select @@version. what is free space in drive and what size of database ?

    check mdf and ldf files in drive.

    https://raresql.com/tag/msg-5591-level-16-state-1-line-1/


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Saturday, April 08, 2017 9:08 AM
  • Thanks for your help.

    When I execute the command, the result is this:

    Microsoft SQL Server 2016 (SP1-GDR) (KB3210089) - 13.0.4202.2 (X64)   Dec 13 2016 05:22:44   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor) 

    Also, in the data folder with my databases, I can see the mdf and ldf files of the database and also I can see the folder of the filestream.

    Saturday, April 08, 2017 9:14 AM
  • RESTORE DATABASE dbname WITH RECOVERY

    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Saturday, April 08, 2017 9:26 AM
  • I get this error:

    Msg 3148, Level 16, State 3, Line 1
    This RESTORE statement is invalid in the current context. The 'Recover Data Only' option is only defined for secondary filegroups when the database is in an online state. When the database is in an offline state filegroups cannot be specified.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Saturday, April 08, 2017 10:17 AM
  • It sounds like you are missing some database files during the recovery.

    Can you do the following. Enable your newly installed SQL Server for file stream. Open up SQL Server Configuration Manager and go to SQL Server Services. Right click on your SQL Server in your right hand pane. Select Properties and file stream as illustrated.

    Once this is done, do the following:

     
    sp_configure 'Show Advanced Options',1
    Reconfigure with Override
    exec sp_configure 'filestream access level',2
    Reconfigure with Override
    
    
    
    

    Try your restore again.

    Saturday, April 08, 2017 11:49 AM
  • I have configured all in configuration manager, and I execute the commands that you let me but the problema is the same, filestream is disabled.
    Saturday, April 08, 2017 6:15 PM
  • This is express edition.what is database size ?

    http://stackoverflow.com/questions/2795911/filestream-in-sql-server-2008-express

    Try to use evaluation Enterprise edition for test purpose.


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Sunday, April 09, 2017 3:24 AM
  • I am using Windows 10 creators update and sql server 2016 express.

    In a virtual machine with Windows 10 (an earlier version of creator updates) it works fine, so the problem I guess it is that filestream has a problem with the new version of Windows.

    So I would like to know if someone knows this possible uncompatibilty problem and if he knows how to solve it.

    Thanks.


    Sunday, April 09, 2017 7:12 AM
  • I have a sql server 2016 database that uses filestream. I have configurated in configuration manager to enable filestream and in sql server management studio, in server propierties, I have filestream full access.

    But when I try to restore a database that uses filestream, I get an error that says that filestream is not eanbled, but this is not true. Is the error 5591.

    I have a virtual machine with windows 10, with an earlier version before creators update, and I have configurated in the same way, and all works correctly, I can restore the database with no problems.

    Also I have noticed this problem after updating windows to creators updates, so I am sure that the problem is that windows 10 creators update has problems with filestream.

    Does Microsoft know about this problem? How I could solve this problem?

    Thanks.


    Monday, April 10, 2017 7:57 AM
  • I am having same issue after update to Win 10 Creators update. I had a working DB with file stream running for weeks. After the update the "Configured values" in server properties says the filestream is enable but if I look in "Running values" they are off.

    Now I reinstalled my MSSQL 2016 express and figured it was during the update it all went bad, now after the re-install I can restore just fine but after a reboot the filestream settings in the "running values" properties window are again set to off. Also checked server properties from configuration tool, also shows activated.

    So something is definitely fubar after the update.

    Tuesday, April 11, 2017 10:10 AM
  • At least I am not the only one with this problem.

    In my case it is a local database for testing, so I can install it in a virtual machine and use this installation, but really it is not a good solution, I would like to can use an installation in my main operative system.

    I hope that they can solve the problema son.

    Wednesday, April 12, 2017 7:52 AM
  • Try to change the service account of the SQL Server process. In my case it helped to set it to "Local System" (as a workaround). Seems to be some rights issue.
    • Marked as answer by ComptonAlvaro Wednesday, April 12, 2017 9:12 AM
    Wednesday, April 12, 2017 8:53 AM
  • @Martin Lepa: that's Works perfectly, thanks so much.
    Wednesday, April 12, 2017 9:06 AM
  • Worked, Thanx for that one.
    Wednesday, April 12, 2017 3:23 PM
  • Thanks, you are safe my life.

    Hope this will not permanent solution from M$.

    Thursday, April 13, 2017 8:06 AM
  • We have reached to our windows team internally for the breaking change in SQL filestream caused with creators update. We have investigated the issue and identified the code change which caused this breaking change. Windows team is working on the fix and plan to release it soon. Until that time, the workaround is to use system account or provide admin permissions to the service account. We will update our SQL Server tiger blog once we have a fix.

    Thanks !!!


    Parikshit

    Friday, April 14, 2017 12:11 AM
  • We have blogged about this issue. We will update the blog post once we have the fix available

    https://blogs.msdn.microsoft.com/sql_server_team/filestream-issues-with-sql-server-on-windows-10-creators-update/


    Parikshit

    Saturday, April 15, 2017 12:04 AM
  • Thank you so much.
    Saturday, April 15, 2017 6:52 AM