I created a Microsoft SQL Server database that has now changed location. (The details are probably unimportant, but for the curious, I originally created it in a folder on an external drive mapped to a particular drive letter, but this mapping no longer exists.)

How can I fix the entry in the Databases list to point to the new MDF file location? I would expect to be able to do this using SQL Server Management Studio Express, but it seems that I can't get the database properties, detach, or even delete the database without a valid connection.

Version info: I think the database was created using SQL Server Express 2005, but I also have SQL Server 2008 and SQL Server 2012 installed, if that matters. The version of SQL Server Management Studio Express I installed is for SQL Server 2005, but I think I can install a different version if I need to. I'm running 64-bit Win 7.

Disclaimer: I know essentially nothing about SQL servers in general, much less MS SQL Server.

EDIT: The invalid database entry eventually disappeared from the management studio, and I'm not sure why. I was able to attach it again using the correct file path, and now it works.

  • @ssnobody That doesn't appear to answer my question. None of the ALTER DATABASE commands (even SET offline) work when the database entry is pointing to a non-existent file location. – Kyle Strand Sep 3 '14 at 20:41
  • Does EXEC master.dbo.sp_detach_db @dbname = N'MyDatabase' work for you from TSQL? – ssnobody Sep 3 '14 at 21:03
  • @ssnobody What is TSQL? I'm using sqlcmd; is that similar enough? – Kyle Strand Sep 3 '14 at 21:06
  • And no, your EXEC line gives the following error: The database '<mydatabasename>' does not exist. Supply a valid database name. To see available databases, use sys.databases.. This happens even if I first do the USE master SELECT name, physical name... stuff from the first step in the page you linked. – Kyle Strand Sep 3 '14 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.