You can usually update the state of the database in sysdatabases so that
SQL Server will allow you to delete it. Be really careful -- you can
wreck your server if you do the wrong thing when you're mucking with
these tables.

-- first make sure you can update system tables
USE master
go
sp_configure 'allow updates', 1
reconfigure with override
go

-- update to emergency mode
UPDATE sysdatabases
SET status = 32768
WHERE name = 'YOUR DB NAME HERE'
go

After that, restart SQL Server. You should be able to drop the db.
However, if you can't, update the status = 0 (normal state), and restart
SQL.

-----Original Message-----
From: Long, Darlene M [mailto:[Email Address Removed]
Sent: Thursday, April 29, 2004 10:33 AM
To: LazyDBA.com Discussion
Subject: Question: Loading state for restore of database


This morning I was trying to restore a backup copy of our production
environment to our test environment when the network had a hiccup. This
has left my test database in a 'loading' state. I don't need the
existing database since it is a test database, but I can't even delete
it. When running sp_helpdb, it does not show up in the list of
databases, although I can see it in enterprise manager. Also, it says
that it is not part of the data collection.

I know there is a way to remove this database, but for the life of me I
can't remember.

Would you please pass on any suggestions?

Thanks in advance,

Darlene
MS Sql Server LazyDBA home page