Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Is the master database still available in SSMS when connecting to the server? –  Michael Perrenoud Dec 8 '12 at 11:15
    
@marc_s the database is not visible or available in query window. –  TheVillageIdiot Dec 8 '12 at 13:10
    
@MichaelPerrenoud yes the system databases are available –  TheVillageIdiot Dec 8 '12 at 13:14
add comment

migrated from stackoverflow.com Dec 8 '12 at 14:17

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
thanks @Michael Perrenoud this worked. But after restarting database server it is showing eftest (Recovery Pending) in object explorer. –  TheVillageIdiot Dec 8 '12 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 –  sqlbattsman Dec 8 '12 at 14:42
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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