December 14, 2010 at 11:21 am
Hello,
A colleague was overwriting a test database with a production database by doing a restore in SSMS. 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?
Thanks,
December 14, 2010 at 11:52 am
Unless you have a backup (of sql scripts to rebuild everything) of the db you started to overwrite you're done for it.
December 14, 2010 at 12:09 pm
The database in question is just a "test" version of our production ERP database. We have a bunch of clients installed that allow users to try things out in the test system before deciding to do them in production system. We periodically overwrite the test system with a production system backup so users are testing with current data. (That's what was being done when the problem happened.) The only differences between the test and production databases are their names and the logins. So it's not hard to setup a new test system but unless I can create it with the same name as the old system, we will need set up new clients for all the users. That's the painful part I'd like to avoid.
Even though we can't access the database, SQL Server still thinks it exists so I don't know how I can recreate it with the same name.
December 14, 2010 at 2:09 pm
Have you tried the restore operation with the corrected path, selecting "overwrite" ?
December 14, 2010 at 2:18 pm
homebrew01 (12/14/2010)
Have you tried the restore operation with the corrected path, selecting "overwrite" ?
I would give this a shot.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 15, 2010 at 6:53 am
It's fixed now.
This morning I tried attaching the database again and it worked. Yesterday attempts to attach didn't work but I had only tried before we restarted SQL Server.
December 15, 2010 at 7:05 am
I guess I misunderstood the question.
Did you first try a restore that failed after 30%? Or did you try a restore that failed instantly?
I was under the impression of the former sceniario, so what happenned?
December 15, 2010 at 7:53 am
I wasn't there when the problem occurred. My understanding is that my coworker intended to restore the production database over the test database but forgot to change the path to point to the test database files. Since it was left pointing at the production database and that database was in use, the restore would have failed. However, when he realized his mistake he tried to cancel the restore anyway. After that the database disappeared. That's all I know. He's a novice. He may have panicked when he thought he was trashing our production database and clicked some wrong buttons in his effort to make it stop.
December 15, 2010 at 8:13 am
dcode (12/15/2010)
I wasn't there when the problem occurred. My understanding is that my coworker intended to restore the production database over the test database but forgot to change the path to point to the test database files. Since it was left pointing at the production database and that database was in use, the restore would have failed. However, when he realized his mistake he tried to cancel the restore anyway. After that the database disappeared. That's all I know. He's a novice. He may have panicked when he thought he was trashing our production database and clicked some wrong buttons in his effort to make it stop.
That makes sense (the description). Good that it is fixed now.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply