Post reply

Database Mirroring Problem

  • May 1, 2007 at 4:17 am

    #117170

    Last week I went on holiday, Today I have come back to find that a support engineer decided to break the database mirroring on a couple of databases, One of which I had no problem getting going again. The other one however, is proving to be a challenge!

    It looks as if the mirroring has been removed, and what was the secondary database is in a state of 'In Recovery'.

    I have reached the point where I have decided to delete the database and set up the mirroring again from scratch, I cannot however delete the database, If I attempt any operation on the database it warns me I can't because a resstore is in progress. Can someone tell me what I need to do to clear this - I'm guessing there's either a flag in msdb or master db or theres a dbcc command to reset the database status 

  • May 1, 2007 at 5:40 am

    #703505

    Just run restore with recovery against the database that will bring it online and then you can drop it.

    David

  • May 1, 2007 at 8:48 am

    #703550

    Thanks for that David , but no go!

    If I attempt to restore I get a message stating

    Msg 3104, Level 16, State 1, Line 1

    RESTORE cannot operate on database xxxxxxxxxx because it is configured for database mirroring. Use ALTER DATABASE to remove mirroring if you intend to restore the database.

    If I attempt any kind of alter database statement i get

    a database is in the Restoring state.

    ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5052)

     

    any other ideas ?

     

     

  • May 1, 2007 at 11:45 am

    #703631

    Sorry no. Fortunately mine haven't gone wrong yet. But i tried it one of my dev boxes and

    RESTORE DATABASE mytestdb WITH RECOVERY

    worked fine on my mirror partner as it should and brought the database online.

    Anyway if you find the solution let us know as its bound to happen to me a some point.

    dVaid

     

  • May 3, 2007 at 2:21 am

    #704010

    On the Partner Server I submitted the command:

    alter database respond_productiion set partner off

    I got the message

    Msg 1416, Level 16, State 1, Line 1

    Database "respond_productiion" is not configured for database mirroring.

    On the Primary I entered the same command and got the same message!

    I then went back to the Partner Server and submitted the command:

    RESTORE DATABASE Respond_productiion WITH RECOVERY

    After 24 minutes I got:

    RESTORE DATABASE successfully processed 0 pages in 1442.218 seconds (0.000 MB/sec).

    So now I can set up my mirroring again (from scratch!)

    I don't understand what went on here under the covers but it seems to have worked!

     

  • April 23, 2009 at 4:36 pm

    #983312

    Try restarting SQL services and then deleting, worked for me.

  • February 21, 2011 at 2:43 pm

    #1289624

    I had the same problem when trying to configure mirroring.

    Instead of using SQL commands I tried using the wizard under "MyDatabase" -> Tasks -> Mirroring and it worked.

    That was the solution for me.

  • June 16, 2011 at 2:02 am

    #1339498

    you can use this:

    ALTER DATABASE [your databasename] SET PARTNER OFF

    Now, to remove mirroring you can execute

    RESTORE DATABASE [your databasename] WITH RECOVERY

  • June 24, 2011 at 7:54 am

    #1343662

    The problem was in the order of the processes.

    1.alter database xxxx set partner of

    2.restore database xxxx with recovery

    3.lastly reconfigure

    To live,to love ,to learn and to live a legacy[/quote]

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

Viewing 9 posts - 1 through 8 (of 8 total)

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