sql-serversql-server-2014-localdb

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


Here is error

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


Solution

  • 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;