Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

A colleague was overwriting a test database with a production database by doing a restore in SSMS (SQL Server 2005). He realized he had set the restore path incorrectly and canceled the operation. At this point the database disappeared from SSMS. The test database .mdf and .ldf files are still in their expected locations. I thought the database had become detached and tried to reattach it. I received the error "cannot attach a database with the same name as an existing database". I tried connecting to the database using various clients using both sa and Window logins without success. We tried restarting SQL Server and rebooting the server but the test database did not reappear.

The only interesting things in the log were three entries saying SQL Server had encountered an occurrence of cachestore flush.

My SQL Server knowledge is limited. Any ideas on how to either get our test database working again or remove all signs of it so I can recreate it with the same name?

share|improve this question
    
The problem has been fixed. See my answer below. –  Doug Dec 16 '10 at 12:52

2 Answers 2

Just do the restore again, checking the REPLACE option in the second tab of the restore screen. When the restore the canceled the old database was gone and the new database was only partially restored. If you refresh the object explorer it should be removed from the list as it isn't there. It might show in a restoring state, I'm not sure how the engine would handle it. In either case the database won't be usable without doing another restore.

share|improve this answer

A consultant (who had come in to do other work) tried to help us out with this problem by doing a restore. I wasn't present and my coworker, a novice a SQL Server, didn't follow what he did. Maybe it was as mrdenny suggested. This did not fix the database not showing up in SSMS. However, the next day I decided to try to attach the database again and this time it worked. I hadn't tried to attach the database after restarting SQL Server so I don't know if it was the restart, the restore, or the combination of the two that got things straightened out.

share|improve this answer
    
This should be edited into the question or left as a comment for mrdenny's answer. Please mark his answer correct if you think what he suggested fixed the problem. –  Chris S Dec 16 '10 at 14:55

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.