Moving database file locations in SQL Server 2008

Question

I have SQL Server 2008 Express edition installed on my machine. I had changed the location of some folders from one drive to another (from F: to E:).

Now when I connect to SQL Server using SSMS, it does not show any database. If I try to attach a database by selecting the file it show me the error that

Cannot attach a database with the same name as an existing database

How can I clean up the no-existing (for SQL Server) databases and re-attach the moved files?

Edit:- In Event Log it is showing errors like following for moved databases:

FCB::Open failed: Could not open file F:\Code\EFTest\App_Data\eftest.mdf for 
file number 0.  OS error: 2(failed to retrieve text for this error. 
Reason: 15105).

master, model,msdb,tempdb databases are available as these are in default location. The unavailable databases were in custom locations (in moved folder).

Answer 1

It's likely that all you need to do is issue the following command for each user database that was moved:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );

I grabbed that line of code from these instructions published by Microsoft. So read those too.

Answer 2

Sounds like the database really does exist, but is just not mounted; does it show up when you carefully refresh the object explorer in SSMS, or when you look at sys.databases?

SELECT * FROM Sys.databases;

If it appears in either place, you'll want to make sure you've backed up the files safely, then DROP the database. Once it's dropped, you can ATTACH the files you've backed up after moving them where you want.

The SQL Server error log will tell you what SQL Server is trying to do about the database when you start the server instance. Showing the log entries for the database that appear at server start up would probably be helpful.