0
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.
Kyle Strand
Posted 2014-09-03T17:04:21.633
Reputation: 1 206
See http://technet.microsoft.com/en-us/magazine/gg452698.aspx
– ssnobody – 2014-09-03T20:26:09.247@ssnobody That doesn't appear to answer my question. None of the
ALTER DATABASEcommands (evenSET offline) work when the database entry is pointing to a non-existent file location. – Kyle Strand – 2014-09-03T20:41:35.837Does
EXEC master.dbo.sp_detach_db @dbname = N'MyDatabase'work for you from TSQL? – ssnobody – 2014-09-03T21:03:47.823@ssnobody What is TSQL? I'm using
sqlcmd; is that similar enough? – Kyle Strand – 2014-09-03T21:06:45.790And no, your
EXECline 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 theUSE master SELECT name, physical name...stuff from the first step in the page you linked. – Kyle Strand – 2014-09-03T21:09:24.390Is the name returned by a
SELECT name, physical_name FROM sys.master_filesthe same as the name you are supplying? – ssnobody – 2014-09-03T21:17:15.850@ssnobody Well, the name I supplied is not present in what it prints out. It says
Changed database context to 'master'., followed by a newline and the wordname, followed by a lot of spaces (I think), followed byphysical_name, followed by more spaces, then a bunch of hyphens, one space, more hyphens, and a newline, then(0 rows affected). – Kyle Strand – 2014-09-03T21:24:04.200@ssnobody Oh, I see, you mean the name returned by just that command. Sorry. The output I described is from the
USE master SELECT name, physical name...stuff. TheSELECTcommand on its own does not list the name I'm supplying as one of the rows. – Kyle Strand – 2014-09-03T22:47:26.253(It appears to only show the built-in
MSSQL11.MSSQLSERVERdatabases.) – Kyle Strand – 2014-09-03T22:48:03.397In fact, using
SELECT name FROM sys.<dbase>, where<dbase>is eitherdatabases,database_files, ormaster_files, I don't see the name of the database I'm looking for in any of the outputs. Should I? – Kyle Strand – 2014-09-03T22:52:01.310I would say yes. Can you try just attach'ing to your moved database and seeing if it returns an error? Attach a Database describes how to do this with SQL Management Studio.
– ssnobody – 2014-09-03T22:58:09.060@ssnobody ....weird, something I did must have deleted the entry from the list of databases I was seeing in SQL Management Studio, because it wasn't there when I opened the studio again. So I was able to just attach it again, and now it works. :/ – Kyle Strand – 2014-09-04T00:32:21.347
I'm guessing the detach worked at some point, glad you got it working again though. – ssnobody – 2014-09-04T01:25:28.137