locked
Backup Job at Mirror Server RRS feed

  • Question

  • Dear All,

    We have 2 servers with witness as third in DB mirror setup. The Principal has backup job runs nightly. Mirror DB can't be backed up. I would like to be able to backup mirror DB (once it fails over to  it, means, when mirror becomes principal). Because during that time, Production DB is Mirror server and it is not being backed up or no backup job is there to run nightly for this server (previously-mirror machine).

    Any advices?

    many Thanks,
    Frenk
    Monday, June 29, 2009 5:59 PM

Answers

  • Its pretty easy actually.  Just add a step before the backup in the job your running that checks the mirror role.  Below is the code I use.  Hope it helps

    If NOT Exists (
        select * from sys.database_mirroring
                    dm join sys.databases d on (dm.database_id=d.database_id)
                    where (d.name=N'Mirror_High_Performance')and mirroring_role_desc = 'Principal'
    )RAISERROR('Mirror_High_Performance is not currently in the primary role', 18, 1)
    Monday, June 29, 2009 9:42 PM
  • I code my jobs to look at the database state.  If the database is online, back it up, otherwise skip it.  That accounts for a mirror as well as anything else that would cause a database to be inaccessible such as being offline, in recovery, recovering, restoring, etc.  Here is my list:

     

    IF DATABASEPROPERTY(@DBName, 'IsDetached') > 0

     

    SELECT @DBMode = 'Detached'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsInLoad') > 0

     

    SELECT @DBMode = 'Loading'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsNotRecovered') > 0

     

    SELECT @DBMode = 'Not Recovered'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsInRecovery') > 0

     

    SELECT @DBMode = 'Recovering'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsSuspect') > 0

     

    SELECT @DBMode = 'Suspect'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsOffline') > 0

     

    SELECT @DBMode = 'Offline'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsEmergencyMode') > 0

     

    SELECT @DBMode = 'Emergency Mode'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsShutDown') > 0

     

    SELECT @DBMode = 'Shut Down (problems during startup)'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsInStandby') > 0

     

    SELECT @DBMode = 'Standby mode'


    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Tuesday, June 30, 2009 10:56 AM
  • I use the not just because RAISERROR is a easy way to just out of the job where I want it.  If exists would work just as well.  On this table only currently mirrored databases will be listed.
    Tuesday, June 30, 2009 2:04 PM

All replies

  • Its pretty easy actually.  Just add a step before the backup in the job your running that checks the mirror role.  Below is the code I use.  Hope it helps

    If NOT Exists (
        select * from sys.database_mirroring
                    dm join sys.databases d on (dm.database_id=d.database_id)
                    where (d.name=N'Mirror_High_Performance')and mirroring_role_desc = 'Principal'
    )RAISERROR('Mirror_High_Performance is not currently in the primary role', 18, 1)
    Monday, June 29, 2009 9:42 PM
  • Do I need to put "NOT"? because above query does not raise error on ordinary database though?

    Thanks,

    Frenk
    Tuesday, June 30, 2009 2:11 AM
  • I code my jobs to look at the database state.  If the database is online, back it up, otherwise skip it.  That accounts for a mirror as well as anything else that would cause a database to be inaccessible such as being offline, in recovery, recovering, restoring, etc.  Here is my list:

     

    IF DATABASEPROPERTY(@DBName, 'IsDetached') > 0

     

    SELECT @DBMode = 'Detached'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsInLoad') > 0

     

    SELECT @DBMode = 'Loading'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsNotRecovered') > 0

     

    SELECT @DBMode = 'Not Recovered'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsInRecovery') > 0

     

    SELECT @DBMode = 'Recovering'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsSuspect') > 0

     

    SELECT @DBMode = 'Suspect'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsOffline') > 0

     

    SELECT @DBMode = 'Offline'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsEmergencyMode') > 0

     

    SELECT @DBMode = 'Emergency Mode'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsShutDown') > 0

     

    SELECT @DBMode = 'Shut Down (problems during startup)'

     

    ELSE IF DATABASEPROPERTY(@DBName, 'IsInStandby') > 0

     

    SELECT @DBMode = 'Standby mode'


    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Tuesday, June 30, 2009 10:56 AM
  • I use the not just because RAISERROR is a easy way to just out of the job where I want it.  If exists would work just as well.  On this table only currently mirrored databases will be listed.
    Tuesday, June 30, 2009 2:04 PM