Post reply

backing up a mirrored SQL 2005 database

  • October 15, 2007 at 7:22 pm

    #153895

    Hi All

    Does anyone have any experience with the following scenario:

    I have a mirrored database setup - Server A is the principal, Server B is the mirror and Server C is the witness.

    There is a backup job (using a maintenance plan) running every night that is set to backup the principal on Server A. That works fine while Server A is the principal. However if the database fails over and Server B becomes principal, the backup job for Server A fails, as 'A' is now the mirror and a mirrored database can't be backed up. Accordingly, i can't set up a backup job on Server B because 99% of the time it is the mirror - and so the backup would fail.

    I assume that more experienced DBAs using database mirroring will have encountered this scenario...if so, does anyone have a suitable backup/ recovery strategy that they would be willing to share?

    Thanks in advance 🙂

  • October 15, 2007 at 10:23 pm

    #742216

    May try this out:

    Set up the backup job on both servers (principal & mirror). Disable/enable them while server failover. The failover event can be detected, so to enable/disable the backup job can be automated.

    Reference: Alerting on Database Mirroring Events

    http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx

  • October 16, 2007 at 1:38 pm

    #742486

    Thanks for the information, I'll have a look at the link and try it out 🙂

  • October 17, 2007 at 7:50 am

    #742719

    When you're setting up your job and want to figure out which node is active this little query can be helpful:

    SELECT PropertyValue from @ServerProperties where PropertyName = 'ComputerNamePhysicalNetBIOS')

    Assign it to a variable and check that to determine which is the mirror. Then set the backup job ot enabled or disabled accordingly.

  • October 17, 2007 at 8:22 am

    #742738

    Just script your backup with T-SQL and set up an agent job on each server to execute that script. The first step in the script can check if the database should be backed up:

    IF EXISTS(SELECT * FROM sys.databases WHERE name = 'YourDatabaseName' AND state_desc = 'ONLINE')

    BEGIN

    BACKUP DATABASE YourDatabaseName .....

    Same thing for log backups.

    Set the job on each server to run on your normal schedule and enable them both.

    Any database that is important - this one is mirrored, I'm guessing it's important - should not have something as critical as backups trusted to some wizard-generated backup maintenance plan. Take control and script the backup yourself.

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • October 17, 2007 at 1:49 pm

    #742870

    Thanks guys - you have provided very useful info, and i take on board the comment about not using the wizard for creating backup jobs. Will try configuring the backup to check the state of the mirror/principal before running. Thanks again 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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