Trying to attach mdf file to localDb throws error at least one file is required

Question

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server '(localdb)\mssqllocaldb'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

At least one file is needed for Database Attach. (Microsoft.SqlServer.Smo)

I am trying to attach this .mdf database file to my LocalDb instance. It's fine if I can to it to SQL Server too. I have .ldf file in the same directory

Answer 1

For completion's sake - Jim's comment solves (half) the problem and gets you going.

The other "half" of the problem is - what if you ultimately want to rename the physical database file? The answer is available in this CodeProject post.

---

Steps:

1. ALTER DATABASE to set the new physical filenames (data file and log file)
   ^{Won't take effect until SQL Server is restarted or the database taken offline and brought back online}

   • ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName', FILENAME = '<Full-Path-Required>\NewDbName.mdf');
   • ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName_log', FILENAME = '<Full-Path-Required>\NewDbName_log.ldf');

2. ALTER DATABASE again to set new logical file names (again, data and log files)
   ^{Takes effect immediately}

   • ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName', NEWNAME = 'NewDbName');
   • ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName_log', NEWNAME = 'NewDbName_log');

3. Take offline and bring back online or restart SQL Server

   • Using SQL Server Management Studio:
     1. Right-click on the renamed database and click Take Offline under Tasks.
     2. Right-click on the (offline) database and click Bring Online under Tasks.
   • Using T-SQL:
     1. ALTER DATABASE [CurrentName] SET OFFLINE WITH ROLLBACK IMMEDIATE; (sets it to offline and disconnects any clients)
     2. ALTER DATABASE [CurrentName] SET ONLINE;

---

Full code:

-- Find "CurrentName" (without quotes) and replace with the current database name
-- Find "NewDbName" (without quotes) and replace with the new database name


USE [CurrentName];

-- Change physical file names:
ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName', FILENAME = '<Full-Path-Required>\NewDbName.mdf');
ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName_log', FILENAME = '<Full-Path-Required>\NewDbName_log.ldf');

-- Change logical names:
ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName', NEWNAME = 'NewDbName');
ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName_log', NEWNAME = 'NewDbName_log');

-- Take offline and back online
USE [master]
GO
ALTER DATABASE [CurrentName] SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- Then navigate to <Full-Path-Required> and rename the files
ALTER DATABASE [CurrentName] SET ONLINE;

Answer 2

If you don't recall the previous filenames, open the .mdf file in a hex editor and at around offset 0x19D you'll see a UTF-16 (2 byte/char) string of that filename