Drop Database

Question

Drop Database

karthik babu

Hall of Fame

Points: 3335
More actions
February 23, 2015 at 10:03 pm

#304575

Comments posted to this topic are about the item Drop Database
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
February 24, 2015 at 2:51 am

This was removed by the editor as SPAM

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

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

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 1

Interesting question , but I have a question : what are the existing différences between DROP DATABASE and the use of sp_detach_db ? I prefer to use sp_detach_db ( because of the possibility of statistics update ) , but it is possible I am going wrong. If someone could give some explanations pro and con , I would appreciate really and thanks berorehand.

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 2

DROP DATABASE deletes the physical disk files used by the database when they are online,

sp_detach_db only removes the database from the current (SQL) server by leaves these files on disk.

https://technet.microsoft.com/en-us/library/ms178613(v=sql.110).aspx

Louis.

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 3

pmadhavapeddi22

SSCertifiable

Points: 5306

February 24, 2015 at 2:18 am

#1779509

Interesting.

thanks for the question

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

Interesting question, thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Hany Helmy SSChampion Points: 13921 More actions · Answer 5

Easy 4 me, had previous experience with it 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

Interesting one. I've never done it, so I learned something today. Thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 7

Thanks Louis for your explanations.

With DROP , the whole database disappears , on the contrary , sp_detach_db keeps the files and enables the possibility to move the location of these files ( useful to attach the database files to another SQL Server instance , maybe on another computer )

barry.mcconnell Default port Points: 1447 More actions · Answer 8

Deleting the database does allow getting rid of the database files even when the database is offline.

There are no facts, only interpretations.
Friedrich Nietzsche

RK Mandava SSCarpal Tunnel Points: 4153 More actions · Answer 9

good question,

here is nice article about that..

http://www.brentozar.com/archive/2015/02/dropping-offline-database-sql-server/

Mike Hays SSCommitted Points: 1871 More actions · Answer 10

Mike Hays

SSCommitted

Points: 1871

February 24, 2015 at 7:23 am

#1779583

Good Question, I had to test this one out....

Bhushan Kulkarni SSCrazy Points: 2829 More actions · Answer 11

Bhushan Kulkarni

SSCrazy

Points: 2829

February 24, 2015 at 7:26 am

#1779586

Nice question

RK Mandava SSCarpal Tunnel Points: 4153 More actions · Answer 12

barry.mcconnell (2/24/2015)
Deleting the database does allow getting rid of the database files even when the database is offline.

no, deleting offline database doesnot delete physical files...

from MS: If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer.

https://msdn.microsoft.com/en-us/library/ms178613(v=sql.100).aspx

and

http://www.brentozar.com/archive/2015/02/dropping-offline-database-sql-server/

barry.mcconnell Default port Points: 1447 More actions · Answer 13

Should have told me that before I just did it. The files are gone.

There are no facts, only interpretations.
Friedrich Nietzsche