3

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?

4
  • I don't believe the problem is related to the logical file name since the FILENAME property is the one that references the file on the disk. I just renamed the logical file name of a database and it's still working fine.
    – Ronaldo
    Mar 8, 2022 at 12:29
  • It seem you brought SQL Server down and renamed A.md to A_Old.mdf. Now after you started SQL Server SQL Server database A is not finding it's datafile that's why it's giving you this error. Mar 8, 2022 at 13:00
  • @Ronaldo you're right, it was the physical file pointing to A. They actually renamed the files on disk with the server offline but did not update the properties in SSMS. Mar 8, 2022 at 14:58
  • @RajeshRanjan correct, this is what actually happened. Mar 8, 2022 at 14:58

3 Answers 3

2

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.
4
  • A is live in production. Is there a way to do this without getting it offline? Also they don't need to co-exist, if I could access A_old on another (dev) server it would be enough. Mar 8, 2022 at 12:29
  • @evilmandarine The thing I'm unsure of / worried about is if you try dropping A_old if it'll remove the MDF / LDF of the production A database (which is why my precautionary step 2 says to move / copy both databases' files). I believe it'll try (because that's the logical path). It may error out if A is currently connected to / in use, or it may succeed, I'm not sure. So I'd try to get a maintenance window off-hours from production, so you could follow the steps I mentioned. Of course once you have A_old database successfully dropped, you don't have to re-attach it to the same server.
    – J.D.
    Mar 8, 2022 at 12:35
  • Yep, I think I'll need the maintenance window. I was trying to avoid that at all costs because this is a different, not technical kind of issue now (tight production schedule). Mar 8, 2022 at 12:39
  • @evilmandarine Gotcha, yea I've been there. Usually a few minutes of downtime trumps risk of data loss and longer downtime, at least when explained to sensible people lol. All in all, the above process should be accomplishable in about 10 minutes, but obviously ask for a longer maintenance window just in case. Also this is where things like Availability Groups are nice because you can failover to a secondary server, and fix the previous primary, then fail back over if needed, etc.
    – J.D.
    Mar 8, 2022 at 12:46
1

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
1
  • Ah that's true. To get access to the data now, you could attach the files as a new database. Cleaning up the old A_old database will likely require more of a process though, but that sounds like it's secondary to your goal anyway. Good idea.
    – J.D.
    Mar 8, 2022 at 12:57
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.