backup failover

• ggrimberg

  Newbie

  Points: 2

  More actions

  June 22, 2009 at 5:45 am

  #222822

  Hi,

  I have primary and mirror sever, and I run full backup once a day and transaction log backup every hour on primary sql 2005. How can I write a script or ssis to automatically determine which server is primary at the moment and run backup on appropriate server,

  Right now if fail over happens and my primary server becomes mirroring server I get an error message that backup is failed and we don’t have ant backup jobs on backup server.

  Thank you.

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  June 22, 2009 at 7:14 am

  #1013268

  looks like you need to start off by querying the value of mirroring_role in table sys.database_mirroring and take it from there.

  ---------------------------------------------------------------------