locked
Backup Scenarios with Mirroring RRS feed

  • Question

  • Hi experts,

     

    I'm new to mirroring, and I've started mirroring one database from my failover cluster to another location as a test of this.

     

    My question is around setting up backup scenarios.  Let's say that i were to set this for automatic failover and late Friday night, a failure happens causing the mirror to become the principal database.  The full backups and the hourly transaction log backups would have been in place only on the downed principal server. 

     

    It seems to me in this scenario that no backups would run unless some DBA took the extra steps to log in during the weekend and set up these backups on the mirror-turned primary database.

     

    Am I guessing this correctly or is there something I'm missing?

     

    thanks!

    Wednesday, August 13, 2008 3:47 PM

Answers

  • Yes, once failover happens since the original principal server will be down backup is not possible. In that case either the DBA can perform it manually or you can configure a job to check the status of the database in mirror server to see if its role is principal, in that case you can take a backup of the database.

    Code Snippet

    If Exists (Select * from sys.database_mirroing where database_id=yourdbid and mirroring_role=1)
    Backup database dbname to disk='Path\filename.bak' With INIT


    Configure it as a job and schedule it for every 1 hour or something. I think this might work but I haven't tested it..

    - Deepak
    Wednesday, August 13, 2008 4:08 PM