Post reply

Cant remove orphaned database

  • January 21, 2009 at 3:47 am

    #126782

    Hey All,

    I have a SQL 2005 server (local instance), with a database which has become orphaned. It was a test which i setup ages ago and at some point (while the local instance was offline), it seems i have removed the files. So cant go back to the files and have no backup.

    Ordinarily i could manually remove the record from the sysdatabases table, but evedently this cannot be done in 2005.

    Any ideas?

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • January 21, 2009 at 3:52 am

    #930197

    Have you tried drop database from T-SQL or just used SSMS?

  • January 21, 2009 at 3:58 am

    #930202

    Steve Jones - Editor (1/21/2009)

    Have you tried drop database from T-SQL or just used SSMS?

    Hey Steve,

    Both, error i get is:

    Msg 3724, Level 16, State 3, Line 1

    Cannot drop the database 'bes' because it is being used for replication.

    But there are no publications or subscriptions setup. Is there a place were I can manually check for any errant table listing?

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • January 21, 2009 at 8:15 am

    #930380

    Check under sys.databases output whether anyone of the following holds true:

    is_published

    is_subscribed

    is_merge_published

    is_distributor

    Also, I liked the below mentioned solution given by noonone at

    http://www.sqlservercentral.com/Forums/Topic115333-7-1.aspx

    You can restore another database backup, that don`t have replications configured, over the database that you want to delete(with the "force restore over existing database" option marked). Doing this you will overwrite the sysobjects lines that saves the replication parameters and the SQL Server will alow you to delete the database.

    Let us know if it helps.

    MJ

  • January 21, 2009 at 9:04 am

    #930441

    MANU (1/21/2009)

    Also, I liked the below mentioned solution given by noonone at

    http://www.sqlservercentral.com/Forums/Topic115333-7-1.aspx

    MJ

    That worked brilliantly MANU, thanks.

    I had seen that posting yesterday, but the restore option was greyed ourt, so I scripted it just now and prosto!

    Cheers 🙂

    Adam Zacks-------------------------------------------Be Nice, Or Leave

Viewing 5 posts - 1 through 4 (of 4 total)

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