how to delete a database under ANY circumstance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to delete a database under ANY circumstance

Author

Topic

DeepGround
Yak Posting Veteran

73 Posts

Posted - 2008-09-18 : 12:56:07

How can I delete a database no matter what state it is in. For example a database that has users in it, has not had the tail end of the log backed up, that is in restore mode, or any other possible excuse sql can come up with why it was not able to delete the database.

I have tons of databases I need to delete and I have no idea what state they could be in at any moment.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 12:59:39

Use DROP DATABASE.You should be either database owner or member of sysadmin fixed server role to do this.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-18 : 13:02:50

If databases is involved in replication,log shipping and Mirroring , have permission issues,you can't drop database.

Use Databasepropertyex to find out everything about database.
Check it in BOL.

--edit--can't

DeepGround
Yak Posting Veteran

73 Posts

Posted - 2008-09-18 : 13:02:53

DROP DATABASE is not powerful enough, it is subject to the same type of responses from Microsoft like this one:

Server: Msg 3702, Level 16, State 3, Line 1
Cannot drop the database 'foo' because it is currently in use.

I need a DESTROY DATABASE option

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-18 : 13:07:58

SET database to SINGLE_USER and drop it if that is a issue.Also do you have backup if something happens.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 13:10:36

Also see this

http://sqlserver2000.databases.aspfaq.com/how-do-i-drop-a-sql-server-database.html

DeepGround
Yak Posting Veteran

73 Posts

Posted - 2008-09-18 : 13:43:02

Even if I succeed in kicking all the users out with Alter database (which doesnt always work) or with the spid huge code snippit I can still get more erros like the following. Microsoft just will not give up on reasons why a database shouldnt be deleted.

Msg 3159, Level 16, State 1, Server DEVPRIME\SQL2005, Line 1
The tail of the log for the database "CA_Zangle_TestPlan_PA" has not been
backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains
work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of
the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Server DEVPRIME\SQL2005, Line 1
RESTORE DATABASE is terminating abnormally.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-18 : 16:29:09

That's a error from restoring a database over the top of an existing database, not from DROP DATABASE.

Personally I like been told that if I may loose important transactions if I carry on, especially when a simple option is given (in the error message) for if that is what I intended to do
RESTORE DATABASE ... WITH REPLACE

quote:
Even if I succeed in kicking all the users out with Alter database (which doesnt always work)

Try ALTER DATABASE ... WITH ROLLBACK IMMEDIATE

--
Gail Shaw
SQL Server MVP

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-18 : 20:40:36

If the database is online, this will usually work:

use master
alter database MyDatabase set offline with rollback immediate
alter database MyDatabase set onlne with rollback immediate
drop database  MyDatabase

If that doesn't work, do this, but you will have to delete the database files manually after you drop the database.

use master

select b.name, a.filename
from
	master.dbo.sysaltfiles a
	join
	master.dbo.sysdatabases b
	on a.dbid = b.dbid
where
	b.name = 'MyDatabase'

alter database MyDatabase set offline with rollback immediate
drop database  MyDatabase

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources