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.

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?

share|improve this question

migrated from stackoverflow.com Mar 25 '13 at 3:43

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

    
So the physical files aren't there anymore, right? Do you still see your database in sys.databases ?? E.g. does SELECT * FROM sys.databases still list your database? –  marc_s Mar 19 '13 at 9:25
    
@Josien thansk for the tip.. I put it there too –  Matt Mar 19 '13 at 9:28
    
@marc_s I tried that now, but it's not showing when I run that query –  Matt Mar 19 '13 at 9:28
    
What does SELECT name, user_access_desc, state_desc FROM sys.databases reveal? –  Mark Storey-Smith Mar 19 '13 at 13:51
    
Why on earth would anyone downvote this post? Could someone please elaborate? Seriously... –  Matt Mar 20 '13 at 1:11

5 Answers 5

If you didn't have permission to see the database then this would be exactly the symptoms. The database would not show up in your views. Are you sure you did not just zap the file(s) of a database you did not have access to? Are you operating as a sysadmin?

Update


Was the database detached or offline? When you say So, we looked around and found that it was still there, but detached what exactly are you talking about? Where did you look, what did you find? Identifying that a database was 'detached' (as opposed to, say, dropped) is not trivial.

share|improve this answer
    
I am indeed a sysadmin –  Matt Mar 19 '13 at 13:33
2  
Since you mentioned it, I have a script on my blog that detects unattached database files in the default data/log folders: voluntarydba.com/post/2012/08/21/… –  Jon Seigel Mar 19 '13 at 16:49
    
Thanks, I will give that a try later when I am in the office. –  Matt Mar 20 '13 at 1:09
    
@JonSeigel, nice one, but unfortunately that turned up nothing for me. I tried something else as well.. restart the server and then create a new DB with that name.. wouldn't let me do it.. very annoying. I think I will have to ignore this situation and create a new db with a different name. –  Matt Mar 20 '13 at 6:31
up vote 4 down vote accepted

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.

share|improve this answer
1  
To be on the safe side, deny the ALTER permission on the database to everyone except 'sa', change the 'sa' password and be selective when giving the password to 'clowns' –  Carol Baker West Mar 25 '13 at 16:52

Query sys.databases. It could be that the database was created with a space at the end of it's name. Run the below code and notice that you can only create one of the databases, and it doesn't matter which one you create first you can only create that one.

create database [test]
create database [test ]
share|improve this answer
    
I think this is a bug, right? –  A-K Mar 19 '13 at 21:50
    
Probably not. I'm assuming that SQL is doing a TRIM on the database name when it's checking to see if the value exists. That would have to be intentional, so it's probably by design. –  mrdenny Mar 20 '13 at 3:19
    
@mrdenny, thanks, but no help there.. the database doesn't show up at all, with any variation in name whatsoever.. and there's only a handful of databases on that server, so I would spot it immediately if it was there. Thanks anyway –  Matt Mar 20 '13 at 6:33

RUN 'DBCC CHECKDB' on master, msdb and the database its self to see what it says. May need to put the system into single user mode to get dbcc to run in repair mode with data loss. Or just restore your backups of master and msdb as appropriate.

share|improve this answer
    
Running that on the master and msdb didn't help and I can't try it on the db itself, because I cannot see the db in the management studio whatsoever... –  Matt Mar 20 '13 at 6:20

For me this was solved by refreshing Databases. I've done some unsuccessful recovery and saw db, that failed to be deleted with same error message: "The Database does not exist on the server."

share|improve this answer
1  
You may want to elaborate on how exactly you go about "refreshing databases". –  mustaccio Mar 4 at 16:56

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.