9

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?

Improve this question
8
  • 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, 2013 at 9:25
  • @Josien thansk for the tip.. I put it there too
    – Matt
    Mar 19, 2013 at 9:28
  • @marc_s I tried that now, but it's not showing when I run that query
    – Matt
    Mar 19, 2013 at 9:28
  • What does SELECT name, user_access_desc, state_desc FROM sys.databases reveal?
    – Mark Storey-Smith
    Mar 19, 2013 at 13:51
  • Why on earth would anyone downvote this post? Could someone please elaborate? Seriously...
    – Matt
    Mar 20, 2013 at 1:11

6 Answers 6

Reset to default
5

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.

Improve this answer
1
  • 4
    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, 2013 at 16:52
4

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.

Improve this answer
4
  • I am indeed a sysadmin
    – Matt
    Mar 19, 2013 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, 2013 at 16:49
  • Thanks, I will give that a try later when I am in the office.
    – Matt
    Mar 20, 2013 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, 2013 at 6:31
3

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 ]
Improve this answer
3
  • I think this is a bug, right?
    – A-K
    Mar 19, 2013 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, 2013 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, 2013 at 6:33
0

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.

Improve this answer
1
  • 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, 2013 at 6:20
0

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."

Improve this answer
1
  • 2
    You may want to elaborate on how exactly you go about "refreshing databases".
    – mustaccio
    Mar 4, 2014 at 16:56
0

I was having this same issue using SQL server 2008r2.

I was trying to duplicate a database (versioning foo) and ended up confusing SQL server. SQL Server Management Studio thought that the database existed (the MDF and LDF did) but the master database didn't think the database existed (didn't show up in sys.databases)

The trick was to rename the MDF and LDF files, then create the database from SQL create database [db name here] then delete the database and finally attach the MDF and LDF files from before.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.