Once Upon A Time there was an Orphan Database…

I needed to drop a formerly-logshipped database on our warm standby server. When attempting to drop it, I found that it failed because it was a logshipped database from a replication publisher. Hmm.

The database was still in standby/read only mode, so I couldn’t run any system sprocs in the database itselt to clean up replication. Uh oh.

My colleague Gina suggested I just run a restore command with recovery to bring it online so I could run those sproc and then strip out replication. Sounds great!

But that didn’t work, because the file it needed to bring it online had been deleted from the file system at some point. Someone must have figured it was OK since this database hadn’t been actively logshipped in a while. Oh boy.

At this point, Think Very Carefully About What You Don’t Want To Do

Now, remember that this is a warm standby server, and is not customer facing. So I considered stopping sql, renaming the files, starting sql, and then seeing if I could drop the database. However, it seemed that this was not likely to work anyway, because there are those pesky records in the master database with metadata, showing that this is a replicated database. And it also just seems like a messy, klugy way to go about the issue.

So Why Not Just Edit the Metadata in Master?

I thought that some updates to that metadata were still possible in the post-sql 9 world, and looked into DAC connections and single user mode. I am very happy to say that situations almost NEVER come up when I have to even consider how to udpate metadata in master. Even on a non-customer facing database, it’s a bad idea.

But even if you want to do it for this, it’s not going to work, since most of the tables were moved into the resource database with SQL 2005 and can’t be updated. Sorry!

A Happy Ending

My colleague Robin very quickly came up with the right solution, which works perfectly. It’s fast, easy, harmless, and requires no updating of system tables or even DAC connections.

• Create a new empty db named x
• Back up db x
• Restore over your read only/standby database with the backup of X (using the with replace option)

VOILA, your database is online and empty, and no longer marked as replicated in the metadata. It’s now totally droppable!

Overall, this was a really fun, if weird little problem to work through.