Restoring database from probably corrupted MDF file

I have a big problem with database which stuck in "Recovery Pending" mode in server with Microsoft Windows Server 2019, SQL Server 2019 and Microsoft SQL Server Management Studio 2018. Some time ago I had to change Filestream paths for mentioned database from one disk to another. I finished this task with success thanks to changing permission for SQL service user and these 2 scripts:


USE [master]
USE [master]
EXEC master.dbo.sp_detach_db @dbname = N'database_name'



USE [master]
CREATE DATABASE [database_name] ON
    (FILENAME = N'C:\Data\database_name.mdf'),
    (FILENAME = N'C:\Data\database_name_log.ldf'),
    (NAME = N'fsFile', FILENAME = N'C:\FileStreams\database_name\fsStream')

Some time ago we realised, mentioned database changed its status to "Recovery Pending" and it stuck like this - I don't know if someone helped it with this or not. Unfortunately, we don't have a backup of this database and also I am not able to create it - "Back Up..." button is greyed and unavailable to click.

The only things I have are MDF and LDF files which has been created after problem with database status (Recovery Pending) appeared.

What I would like to do is:

  1. restore this database on a different server through mentioned MDF and LDF files,
  2. fix database with status "Recovery Pending" on main server.

What problems I met:

  1. While restoring database from MDF file through SSMS on the new server with "Attaching" script above I get this:

Msg 5120, Level 16, State 101, Line 4 Unable to open the physical file "C:\FileStreams\database_name\fsStream". Operating system error 2: "2(The system cannot find the file specified.)". Msg 1802, Level 16, State 7, Line 4 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

I created this path on new server and pasted there all files from main server which were used there including filestream.hdr file but it didn't help. I also gave full permissions to this path for all users on the new server but it also didn't help.

  1. While try to attach database with Database > RMB > Attach > choose .MDF file I get this:

`Attach database failed for Server 'Server_Name'. (Microsoft.SqlServer.Smo)


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Unable to open the physical file "C:\FileStreams\database_name\fsStream". Operating system error 2: "2(The system cannot find the file specified.)". A file activation error occurred. The physical file name 'C:\FileStreams\database_name\fsStream' may be incorrect. Diagnose and correct additional errors, and retry the operation. Could not open new database 'database_name'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 5120)`

I would like to focus on restoring it on the new server and than I will try to fix database on the main server.

I know there are planty of similar question in the internet but my situation looks quite different - I think I destroyed something because of filestream changes.

To fix the Database recovery pending state, follow the below manual approach:

Put the database in emergency mode and start a forced repair process
Mark the database into emergency mode, and then disconnect and reconnect the primary database.
Also, check your memory space or disk storage.

Please refer to the below article for more details