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?
Best Answer
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.