How to rename logical file names in recovery pending database that cannot be recovered?

Question

Here's the situation:

• Someone renamed database A to A_old in SSMS
• They did not rename the physical nor logical file; physical file was renamed somehow to A_old.mdf
• They then created a new database called A that has A.mdf as filename
• However A_old database is now in recovery mode because it still has logical file name A.mdf

I tried to rename the logical file with a script (ALTER DATABASE ... MODIFY FILE), getting and not getting the database offline, as admin in SSMS, but still getting an error:

Database 'A_OLD' cannot be opened due to inaccessible files or insufficient memory or disk space.

There is disk space and memory. If I set offline/online this does not work, because it keeps trying to attach the database to the new one's file which is using it. Also tried to copy the mdf file and manually attach it using SSMS but it does not work because it looks for the file without the _old suffix.

I'm running out of ideas. Is there any way to recover this database?

Answer 1

If your goal is to have both databases co-exist then I would try the following:

1. Take both the A database and A_old database offline if possible.
2. Make copies or temporarily move their MDF and LDF files. (This is a precautionary measure and may be a little overkill.)
3. Drop the A_old database.
4. Put back the MDF and LDF files of both databases.
5. Turn A back online (and make sure it's still working properly).
6. Attach a database and select the MDF and LDF for A_old which if I understand you correctly is now called "A_old.mdf" (note the LDF file will need to have a different name than the new A database's LDF file too).
7. Verify A_old is working properly now.

Answer 2

As I only needed the data I ended up managing to get it on the same server running this command (the SSMS Attach... did not work but this is the script it generated):

USE [master]
GO
CREATE DATABASE [A_OLD_Copy] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\A_OLD.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\A_log_OLD.ldf' )
 FOR ATTACH
GO

Answer 3

You should be able to just change the path to that file

ALTER DATABASE A_Old
MODIFY FILE (
  NAME = [A.mdf],
  FILENAME = 'C:\Whatever Path Here\A_Old.mdf'
);

You can also specify a new logical name for the file, although this is not actually necessary

ALTER DATABASE A_Old
MODIFY FILE (
  NAME = [A.mdf],
  NEWNAME = [A_Old.mdf],
  FILENAME = 'C:\Whatever Path Here\A_Old.mdf'
);

You may need to set the database OFFLINE first.