SQL Server Performance

BackUp en SQL2005 con Mirroring

Discussion in 'Preguntas sobre SQL Server en Español.' started by The Pipo, Jun 11, 2007.

  1. The Pipo New Member

    Hola gente como andan?

    Les quiero hacer una pregunta.

    Tengo una base de datos en SQL2005 a la cual se le implemento la funsion de mirroring.

    Mi consulta es, puedo hacer un Back up full de la base estando implementado el mirror?

    Trate de hacerlo y no pude, la funcion de backup desde el sql estudio no esta, y por linea de codigo me arroja un error:

    The database "NN" cannot be opened. It is acting as a mirror database.

    Alguna sugerencia??

    The Pipo Dba.
  2. satya Moderator

    I answer this in English as the error specified soudns like you are trying to backup the mirror database which is a default message when trying to access as it is in exclusive use of DB mirroing.
    #
    If so then avoid using the backup for mirror server.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. The Pipo New Member

    Thanks for your answer, in this moment i am searching how to do this.

    If yo know the sentence to do this backup mirror please copy at foro.

    Thanks for all !!

    ThePipo

    ESPAÑOL:
    Gracias por tu respuesta, en este momento me encuentro buscando lo que decis.

    Si conoces la sentencia de SQL para hacer el backup mirror por favor copialo en el foro.

    Saludos

    ThePipo

    The Pipo Dba.
  4. satya Moderator

    Why do you need to backup the mirror, if you have the job on primary server then it should be sufficient to have a similar copy.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. The Pipo New Member

    we think is more security to have 2 backup

    one with the mirror and two with a full backup of the database.

    But i dont know if this is posible.

    I wait your answer..

    Thaks

    ESPAÑOL:
    Pensamos que es mas seguro tener 2 backups

    One con el Mirror y otro con el Full Back Up de la base de datos

    Pero no se si esto es posible.

    Espero su respuesta

    Gracias


    The Pipo Dba.
  6. satya Moderator

    I don't think you need to perform backup for mirror server, as I said once you have the backup from PRIMARY then nothing but you do on mirror is a duplication, I feel nothing but wasting disk space and resources.

    As an integrity for that backup you might try to copy and restore on a seperate location without any access to any logins, in order to ensure the backup is healthy and can be restored.

    (May be Luis can be help here to translate to Spanish, ta).

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. The Pipo New Member

    Thaks Satya for your answer.

    And I share what vos decis. But in the case that the mirror fail, which basket our opcion? If a backup failure I have that of the I gave previous. Always I have a plan B, but in the case of the mirror, if fails the same one I am in the oven! Ha...

    In short, you have idea if I can do a backup of the base that this in mirroring?


    ESPAÑOL:
    y comparto lo que vos decis.
    Pero en el caso que el mirror falle, cual sera nuestra opcion?
    Si un backup falla tengo el del dia anterior.
    Siempre tengo un plan B, pero en el caso del mirror, si falla el mismo estoy en el horno!
    ja...

    En fin, tenes idea si puedo hacer un backup de la base que esta en mirroring?


    The Pipo Dba.
  8. satya Moderator

    The mirrored copy is a standby copy that can not be accessed directly. It is used only for failover situations. With SQL Server 2005 Enterprise Edition you can also use database snapshots with the mirror, fyihttp://msdn2.microsoft.com/en-us/library/ms175511.aspx in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  9. The Pipo New Member

    Thank you very much!

    ESPAÑOL:
    Muchisimas GRACIAS!!

    The Pipo Dba.

Share This Page