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:
Detaching
USE [master]
GO
ALTER DATABASE [database_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'database_name'
GO
and
Attaching
USE [master]
GO
CREATE DATABASE [database_name] ON
(FILENAME = N'C:\Data\database_name.mdf'),
(FILENAME = N'C:\Data\database_name_log.ldf'),
FILEGROUP [fsGroup] CONTAINS FILESTREAM DEFAULT
(NAME = N'fsFile', FILENAME = N'C:\FileStreams\database_name\fsStream')
FOR ATTACH
GO
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:
- restore this database on a different server through mentioned MDF and LDF files,
- fix database with status "Recovery Pending" on main server.
What problems I met:
- 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.
- 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)
ADDITIONAL INFORMATION:
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.