February 18, 2010 at 6:23 pm
I just created your scenario and I had no problem dropping the database using a DROP DATABASE command. 2005 SP3.
What method are you trying to use and what error do you get?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 18, 2010 at 7:02 pm
Restoring a old master and dropping the DB or rebuild system database is a good option
February 18, 2010 at 7:05 pm
nilmov (2/18/2010)
Restoring a old master and dropping the DB or rebuild system database is a good option
Why on earth would you advise doing that? You don't even know why the delete is failing or how it's being attempted!
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 18, 2010 at 10:44 pm
Paul .
I agree . I will take back my words.
My action item is review the output of drop database command .
February 19, 2010 at 9:02 am
In case anybody else has a similar brain-cramp, here's what I did to get rid of the database who's .mdf file was gone...
- created a database on a different server with the same name as my missing database
- backed it up
- detached it and copied the .mdf and .bak file to the original db server
- put the .mdf file where it should have been; in my case I got my LUN back w/ the same drive letter, I don't think it would have mattered though if I put it elsewhere
- restored over the database pointing to the "new" .mdf and the original .ldf.
- dropped the database
Moral of the story: always check what's on a LUN before you tell the SAN guys it's okay to delete it.
And back up your databases, test the restore and move it off the server - fancy fonts and all. 😉
February 19, 2010 at 9:11 am
I wish I'd kept screenshots, I tried to detach and to drop from the GUI and by query. Each time it failed, can't remember the exact verbiage but it couldn't access the mdf file - insufficient space or permissions - while really it just wasn't there.
I'd test it again - but I dont' want to call the SAN guy to ask him to drop that LUN again.
btw- SQL 2005 RTM.
February 19, 2010 at 11:45 pm
"Shoot, Aim, Ready." Heh. Not a best practice for DB Corruption and Recovery.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2010 at 3:13 am
My policy whenever I create a new database is:
1 Change recovery mode to full
2 Change default size (another topic)
3 Backup the database immediately
I already have a job setup to backup all user database logs hourly and databases once a day. (They are small enough and we have the space to store them so...) I find it saves time - and worry - in the long run.
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
February 24, 2010 at 8:14 am
I'd like to chime in here on the original question of the lost .MDF file....
HOW the data file was lost could make a difference.
APEX SQL Recover will go out and read where it thinks the data used to be on disk and recover the DB. Here's the key....As long as that particular disk space has not been over-written, it may work.
I know, total shot in the dark, but a possible option.
APEX has a specific recover option to "recover from a corrupted db or detached .mdf file".
Apex is fairly inexpensive too.
Tim White
February 24, 2010 at 9:30 am
I just tried to test out the Apex solution and it did not work.
I tried several scenarios using detach, offline, moving the .mdf, etc....
Sorry !
Tim White
June 25, 2015 at 11:38 pm
This was removed by the editor as SPAM
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply