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

I've had to move/rename DBs several times. In case you're in the same boat, here's a script that uses variables to avoid typing the new/old names over and over.

It uses the same logic from Jesse's answer, other than automatically starting the DB back up for you. I assume you need to turn it back on after moving/renaming the physical files, hence the removal of that statement. Please comment if this assumption is incorrect.

However, to reflect the logical rename in SSMS, you still need to right click -> rename. That appears to be the same without using the EXECUTE/REPLACE method below.

---------- CHANGE THESE ----------
-- Keep names identical to only move locations
DECLARE @CurrDbName AS varchar(255) = 'CurrentDbName'
DECLARE @NewDbName AS varchar(255) = 'NewDbName'
DECLARE @PathToFolder AS varchar(255) = '<FullPathMinusFilename>\'


---------- DECLARE TEMPLATES ----------
-- Use DB
DECLARE @USE_DB AS varchar(255) = 'USE [{CurrDbName}]'

-- Change physical file names
DECLARE @SET_PHYS_MDF AS varchar(255) = 'ALTER DATABASE [{CurrDbName}] MODIFY FILE (NAME = ''{CurrDbName}'', FILENAME = ''{PathToFolder}{NewDbName}.mdf'')'
DECLARE @SET_PHYS_LDF AS varchar(255) = 'ALTER DATABASE [{CurrDbName}] MODIFY FILE (NAME = ''{CurrDbName}_log'', FILENAME = ''{PathToFolder}{NewDbName}_log.ldf'')'

-- Change logical names (LOG = "logical", not "log")
If (@CurrDbName != @NewDbName)
BEGIN
    DECLARE @SET_LOG_MDF AS varchar(255) = 'ALTER DATABASE [{CurrDbName}] MODIFY FILE (NAME = ''{CurrDbName}'', NEWNAME = ''{NewDbName}'')'
    DECLARE @SET_LOG_LDF AS varchar(255) = 'ALTER DATABASE [{CurrDbName}] MODIFY FILE (NAME = ''{CurrDbName}_log'', NEWNAME = ''{NewDbName}_log'')'
END

-- Take offline
DECLARE @SET_OFFLINE AS varchar(255) = 'ALTER DATABASE [{CurrDbName}] SET OFFLINE WITH ROLLBACK IMMEDIATE'


---------- START DOING STUFF ----------
DECLARE @SQL_SCRIPT AS varchar(255)

-- Use DB
SET @SQL_SCRIPT = REPLACE(@USE_DB, '{CurrDbName}', @CurrDbName)
EXECUTE (@SQL_SCRIPT)

-- Change physical file names
SET @SQL_SCRIPT = REPLACE(REPLACE(REPLACE(@SET_PHYS_MDF, '{CurrDbName}', @CurrDbName), '{NewDbName}', @NewDbName), '{PathToFolder}', @PathToFolder)
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = REPLACE(REPLACE(REPLACE(@SET_PHYS_LDF, '{CurrDbName}', @CurrDbName), '{NewDbName}', @NewDbName), '{PathToFolder}', @PathToFolder)
EXECUTE (@SQL_SCRIPT)

-- Change logical names (LOG = "logical", not "log")
If (@CurrDbName != @NewDbName)
BEGIN
    SET @SQL_SCRIPT = REPLACE(REPLACE(@SET_LOG_MDF, '{CurrDbName}', @CurrDbName), '{NewDbName}', @NewDbName)
    EXECUTE (@SQL_SCRIPT)
    SET @SQL_SCRIPT = REPLACE(REPLACE(@SET_LOG_LDF, '{CurrDbName}', @CurrDbName), '{NewDbName}', @NewDbName)
    EXECUTE (@SQL_SCRIPT)
END

-- Take offline
USE [master]
SET @SQL_SCRIPT = REPLACE(@SET_OFFLINE, '{CurrDbName}', @CurrDbName)
EXECUTE (@SQL_SCRIPT)

-- Now turn off the database, rename/move physical files, and bring the database back online

This is my first answer, apologies if it's not of sufficient quality.

Answer 3

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

Answer 4

Command line turned out to be much more forgiving with the renamed files. Note that this isn't a fire-and-forget script...run each portion separately, paying attention to the names that need to be changed:

--#1 Attach the db
USE [master]
GO
CREATE DATABASE RenamedDB ON 
( FILENAME = N'<PathToRenamedFile>\renamedDBFile.mdf' ),
( FILENAME = N'<PathToRenamedFile>\renamedDBFile_log.ldf' )
FOR ATTACH
GO


--#2 Get the old logical file names:
USE RenamedDB
select * from sys.database_files


--#3 Rename the old logical files
ALTER DATABASE RenamedDB MODIFY FILE (NAME=N'OldLogicalDBName', NEWNAME=N'renamedDBFile')
GO
ALTER DATABASE RenamedDB MODIFY FILE (NAME=N'OldLogicalLogName', NEWNAME=N'renamedDBFile_log')
GO

--#4 check for the new names
select * from sys.database_files

Answer 5

None of these answers were quick to the point answers so I thought I would just add my answer to point out my findings (based off of everyone's contributions here)...

The Situation:

You have a database file and a log file but not a backup of them. You are trying to ATTACH the database (more than likely in an effort to recover from a server that went down).

The Problem:

You have changed the name of the MDF and LDF files to something different than they were originally. You need to rename them back to the original names then try to ATTACH.

How To Rename the DB files (the easy way):

1. After you have ATTACHED the MDF and LDF files successfully you then want to make a BAK (backup) file by backing up the database.
2. Next you want to drop/delete the database from the SQL server.
3. Next you want to RESTORE the database. This is where you can go into the FILES section (on left) that will let you change the Restore As file name to what you want the MDF and LDF files to be named as.
4. I would then go ahead and make ANOTHER backup of that new database again so that this time the backup contains the correct file names you want.