Got a really weird, annoying problem.. Somehow the instance of SQL Server 2008 R2 running on our server has gotten somewhat corrupted.
First, we noticed that the database we created yesterday was missing. So, we looked around and found that it was still there, but detached. So, we tried to attach the mdf but got a message which was something like The file is currently in use.
I thought that was odd, so restarted SQL Server. Same thing… okay, time for drastic measures… so, I stopped the service, zipped up the mdf, started the service, unzipped it and then tried to restore it. The above message was gone, but then I got:
Cannot attach a database with the same name as an existing database
Ouch. Of course it's not showing in the database explorer, so no idea what's going on… last resort:
DROP DATABASE [DatabaseName]
Of course that didn't work.. that tells me the database does not exist. So, I'm stuck… at one point SQL Server thinks the database does exist and at another point it thinks the db does not exist.. obviously it's in a state of confusion.
Has anyone seen this before? Got any ideas on how to fix it?
Best Answer
Right.. I figured it out.. some clown here (who won't own up to it) renamed the db so that its name in SSMS is different to the mdf and ldf file names.. and not just a bit different... completely different. LOL. Thanks all for your helpful suggestions anyway.