3

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).

3
  • Is the master database still available in SSMS when connecting to the server? Dec 8, 2012 at 11:15
  • @marc_s the database is not visible or available in query window. Dec 8, 2012 at 13:10
  • @MichaelPerrenoud yes the system databases are available Dec 8, 2012 at 13:14

2 Answers 2

3

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.

2
  • thanks @Michael Perrenoud this worked. But after restarting database server it is showing eftest (Recovery Pending) in object explorer. Dec 8, 2012 at 13:53
  • That means that SQL cannot run restart recovery on that database for some reason. A common reason is that the log file is missing or corrupt. Did you run the above statement for the log file as well? If the log file was also moved (.ldf) it will require the same MODIFY FILE statement Dec 8, 2012 at 14:42
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.