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
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:
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');
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');
Take offline and bring back online or restart SQL Server
Take Offline
under Tasks
.Bring Online
under Tasks
.ALTER DATABASE [CurrentName] SET OFFLINE WITH ROLLBACK IMMEDIATE;
(sets it to offline and disconnects any clients)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;