locked
Move Data and Log file of mirrored database on mirror server RRS feed

  • Question

  • Hi

    To move data and Log file of mirrored database on mirror server, we use following stpes-

    1.  USe Alter Database command to update new path of data file in master DB.
    2.  Then stop sql server service ( can not take database offline becoz used in mirroring).
    3.  Now copy mdf and ldf file to new location.
    4.  Now start sql service.

    But in above process, we are stopping sql server service which can affect other mirrored databases also if thre are
    more databases in mirroring session on mirror server.

    And we also know that we can not take database offline becoz it is used in mirroring.

    So is there any way to avoid stop sql service to move data and Log file of mirrored database on mirror server????


    Mark As Answer If My Reply Is Helpful
    Thanks
    Neeraj Bhandari (MCTS - Sql Server 2008)

    Friday, October 11, 2013 1:06 PM

Answers

  • Hi, 

    There is no other way You should stop sql sever services ! ,De-tach,attach will not work as the database is in restoring state . 

    The only other way is to break the database mirroring session, and re-establish mirror after performing restore to the correct location.Which you are aware of !

    -----------------
    Thanks,Suhas Vallala

    Friday, October 11, 2013 1:15 PM
  • the only other way (obvious to me) is to reconfigure mirroring from scratch.  Break the mirror, drop the mirrored database, re-restore it from backups on the correct drive, then re-mirror it.

    Your post covers the only other way I know of.


    Thanks, Andrew
    My blog...

    Friday, October 11, 2013 1:17 PM
  • So is there any way to avoid stop sql service to move data and Log file of mirrored database on mirror server????


    Mark As Answer If My Reply Is Helpful
    Thanks
    Neeraj Bhandari (MCTS - Sql Server 2008)

    Hello,

    If the files are used you cannot copy or move it to other location,I dont know what proces you are following may be by making pricipal mirror and then moving files ,in this case if you have other DB configured in mirroring they will be affected


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, October 11, 2013 1:19 PM
  • to move the mdf/ldf file we have to stop the SQL Servervices, there is no other option,


    Ramesh Babu Vavilla MCTS,MSBI

    Friday, October 11, 2013 1:48 PM

All replies

  • Hi, 

    There is no other way You should stop sql sever services ! ,De-tach,attach will not work as the database is in restoring state . 

    The only other way is to break the database mirroring session, and re-establish mirror after performing restore to the correct location.Which you are aware of !

    -----------------
    Thanks,Suhas Vallala

    Friday, October 11, 2013 1:15 PM
  • the only other way (obvious to me) is to reconfigure mirroring from scratch.  Break the mirror, drop the mirrored database, re-restore it from backups on the correct drive, then re-mirror it.

    Your post covers the only other way I know of.


    Thanks, Andrew
    My blog...

    Friday, October 11, 2013 1:17 PM
  • So is there any way to avoid stop sql service to move data and Log file of mirrored database on mirror server????


    Mark As Answer If My Reply Is Helpful
    Thanks
    Neeraj Bhandari (MCTS - Sql Server 2008)

    Hello,

    If the files are used you cannot copy or move it to other location,I dont know what proces you are following may be by making pricipal mirror and then moving files ,in this case if you have other DB configured in mirroring they will be affected


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, October 11, 2013 1:19 PM
  • to move the mdf/ldf file we have to stop the SQL Servervices, there is no other option,


    Ramesh Babu Vavilla MCTS,MSBI

    Friday, October 11, 2013 1:48 PM