Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

During an aborted TFS import (2008 into 2010), I have managed to "lose" a database in 2008. The database is not visible in Management Studio, but the SQL Server exe has a handle on the .mdf file (according to UnLocker), it says it cannot attach it because the file is in use and it cannot attach a copy of the file (created when SQL Server was stopped), as it says a DB of the same name is already attached.

Given I am using the same TFS admin account I have always used and have always been able to see the database in, why is this database missing and, more importantly, how do I get it back again?

share|improve this question

3 Answers 3

This is quite strange!

Is it possible you have another named instance of sql server running & this has the database attached?

Are you logging into sql server as a sysadmin or sa & still can't see the database? If you're not sa & your not a user in that database, then you may not be able to see it with your current login.
The VIEW ANY DATABASE permission may be denied on your current login, or the permission may have been revoked from the public role.

The easiest way to fix this is to make your your TFS user account (login) is a user on the database.

share|improve this answer
    
I have tried logging in as the TFS Service user and the local machine admin, both of whom should be able to see the DB. Is there a command line way of adding a user to a DB that is not visible in the Management Studio? –  Colin Desmond Dec 8 '09 at 9:35
    
if you cant see it in management studio, then you probably dont have access. what do you see in the sys.databases dmv? are you sure your only running one sql instance –  Nick Kavadias Dec 10 '09 at 12:51

May sound silly, but... have you tried restarting the SQL Server service?

Sometimes file locks just get stuck for no apparent reason.

share|improve this answer

Do you have access to view all database activity i.e. to see what open connections there are are to that database? If you can see a process working against that database name you could try killing the process.

I've had this kind of issue with a DB being locked by Management Studio for no clear reason and killing the SPID sorted it out so that I could carry on with the maintenance I was trying to do.

Try running an sp_who2 in the query window.

share|improve this answer

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.