Cannot delete db on sqlserver

Question

Cannot delete db on sqlserver

jolan.mahinay

Ten Centuries

Points: 1111
More actions
November 22, 2010 at 8:46 pm

#243195

hello to all
i would like to ask if ever their is a possible solutions on this particular problem, here the scenario we have a db name test on the test server and accidentally deleted where the file is being stored without detaching first on the sqlserver, now our problem is we cannot able to delete the database test on the sqlserver and his status is in recovery mode.."The physical file is deleted"..please help..
thanks

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

robert.williams 82816 Old Hand Points: 380 More actions · Answer 1

If you have a backup, then restore it, then delete it. Sorry, I just reread your statement. Did you delete the file or the whole drive where the file lived?

jolan.mahinay Ten Centuries Points: 1111 More actions · Answer 2

yes the mdf and log file deleted on the drive where he is stored..

jolan.mahinay Ten Centuries Points: 1111 More actions · Answer 3

we dont need that database anymore but it causes our server to hung up..how can we detach that database on the sqlserver..

thanks

robert.williams 82816 Old Hand Points: 380 More actions · Answer 4

Ok.

How did you manage to delete the DB when it was online and in use. I just tested your theory and could not delete the individual files while the DB was online.

So I took the DB offline and then managed to delete the files.

then I could delete the DB.

What state is your DB in at present? Have you tried taking it offline?

jolan.mahinay Ten Centuries Points: 1111 More actions · Answer 5

i asked again the person who did this, he told me that he took the db to offline then he delete the file manually where it was stored and then he refresh the sqlserver there it shows a gray color..

thanks

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

jolan.mahinay (11/22/2010)
he took the db to offline then he delete the file manually where it was stored and then he refresh the sqlserver there it shows a gray color..

it means that deletion has been done intentionally.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

jolan.mahinay Ten Centuries Points: 1111 More actions · Answer 7

whether it is intentionally or accidentally what we need is the solutions if there is..if there is none then we will going to reinstall the sqlserver that's our last option to get rid that scenario.

thanks

Adiga One Orange Chip Points: 27284 More actions · Answer 8

Did you try detaching the database or dropping the database?

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

grahamc SSCertifiable Points: 6762 More actions · Answer 9

put the files back, start the DB and then delete properly

failing that, try to restore a DB over that one, same name, but moving the files, then delete properly.

ps. One Orange Chip Points: 29422 More actions · Answer 10

tried to simulate your situation.

create database testdd on primary(name='testdd', filename='c:\testdd.mdf')

log on (name ='testddlog', filename='c:\testddlog.ldf')

-- setting database to offline mode

alter database testdd set offline

-- now deleting mdf file

xp_cmdshell 'del "c:\testdd.mdf"'

-- trying to bring db online

alter database testdd set online -- you get error here

drop database testdd -- database is dropped successfully

Pradeep Singh

jolan.mahinay Ten Centuries Points: 1111 More actions · Answer 11

sir we restore the file from the bin then the status of the db is in recovery\read only, what would be the next step cause when we try to right click that db the server hung up so we tried to wait until its recovered but we waited for almost 6 hrs but no luck still on its status of in recovery\read only, the mdf is only about 1 gig.

thanks

jolan.mahinay Ten Centuries Points: 1111 More actions · Answer 12

we could not execute detach or dropping because the db is on recovery\read only mode, and we tried also the sp_detach_db but no luck..

thanks

ps. One Orange Chip Points: 29422 More actions · Answer 13

jolan.mahinay (11/23/2010)
we restore the file from the bin then the status of the db is in recovery\read only, what would be the next step cause when we try to right click that db the server hung up so we tried to wait until its recovered but we waited for almost 6 hrs but no luck still on its status of in recovery\read only, the mdf is only about 1 gig.

can u post the command used to restore the database?

also can u run this command?

restore database yourdb with recovery

Pradeep Singh

Leo.Miller SSChampion Points: 13051 More actions · Answer 14

Have you tried doing a restore with REPLACE? You should be able to use any database backup as long as you make the database name match and use WITH MOVE to put the files in the right place with the right names.

If this doesn't work youmay need to retore the database to a new location with a new name, then take it off line and COPY (this is very important other wise you just have the same issue again) the files to the old location, then restart the service.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.