locked
How to Only Backup Principal, not mirror RRS feed

  • Question

  • I have a Server Agent Job that does a full backup of my principal database.  I then have a separate job which does an incremental backup of the log files every 20 mins throughout the working day - the incremental backup sends me an email if it fails.

    If I establish a failover to the mirror server, the backups continually try to run, sending me an email every 20 mins telling me that it can't back up the database as it's a Mirror.

    Is there any way to script a method in the job that checks to see if the DB is a principal or mirror.  if it's a principal I want the backup to happen, if it's a mirror I want it to skip the job.

    Thanks in advance to anyone that can help

    Niels
    Friday, April 13, 2007 11:50 AM

Answers

  • No need to apologise!

     

    It's my mistake, you need to enclose the database name in single quotes, e.g.

     

    DATABASEPROPERTYEX('test_sql_db', 'Updateability') ... etc.

    Tuesday, April 17, 2007 10:55 AM

All replies

  • Hi Niels,

     

    How did you confiure the Log backups (e.g. using backup log or logshipping backups) and what is the exact error message that you are geting?

     

     

    regards

    Jag

    Sunday, April 15, 2007 12:04 PM
  • Hi Again,

     

    I used logshipping to setup the transaction log backups. Here's the message I get when running Log backups on the mirror database. These are just messages and get swallowed up and do not generate any alerts.

     

    Date  15.04.2007 14:04:00
    Log  Job History (DBA Log Shipping Backup <databasename>)

    Step ID  1
    Server  <servername>
    Job Name  DBA Log Shipping Backup <databasename>
    Step Name  Log shipping backup log job step.
    Duration  00:00:30
    Sql Severity  0
    Sql Message ID  0
    Operator Emailed  
    Operator Net sent  
    Operator Paged  
    Retries Attempted  0

    Message
    2007-04-15 14:04:30.39 Deleting old log backup files. Primary Database: '<databasename>'
    2007-04-15 14:04:30.41 A transactional log backup could not be generated because the database is in either NORECOVERY mode or STANDBY mode. Primary Database: '<databasename>'

     

    regards

     

    Jag

    • Proposed as answer by vamshi kiran Monday, May 22, 2017 10:37 AM
    • Unproposed as answer by vamshi kiran Monday, May 22, 2017 10:37 AM
    Sunday, April 15, 2007 12:39 PM
  • As they are managed using db mirroring and why you are enabling the Transaction log backups on mirror additonally, I think it is not required.

    http://msdn2.microsoft.com/en-us/library/ms187016.aspx fyi.

    Sunday, April 15, 2007 6:41 PM
  • You can set up a job to only back up the database if it's online and read/writeable like this

     

     
      IF UPPER(CAST(DATABASEPROPERTYEX(Database_Name,'Updateability') as VARCHAR(20))) = 'READ_WRITE'
                AND UPPER(CAST(DATABASEPROPERTYEX(Database_Name,'Status') as VARCHAR(20))) = 'ONLINE'
            BEGIN

            .. put backup command here

          END

     

     

    then you can schedule this and run it on the mirror all the time. It will only back up the database if it's the principal. The advantage of this is that you don't have to do anything if failover has happened, you know your databases will get backed up automatically.

    Monday, April 16, 2007 7:54 AM
  • Thanks for the help Tom,

    This looks exactly like what I need but when I try using it I get the following error:

    Executed as user: adminuser. Invalid column name 'test_sql_DB'. [SQLSTATE 42S22] (Error 207) Invalid column name 'test_sql_DB'. [SQLSTATE 42S22] (Error 207). The step failed.

    The Job has the following script:

    IF UPPER(CAST(DATABASEPROPERTYEX(test_sql_DB,'Updateability') as VARCHAR(20))) = 'READ_WRITE' AND UPPER(CAST(DATABASEPROPERTYEX(test_sql_DB,'Status') as VARCHAR(20))) = 'ONLINE'
    BEGIN
    BACKUP DATABASE [test_sql_DB] TO DISK = N'F:\SQLBACKUP\test_sql_DB_Full_DB_Backup.BAK' WITH NOFORMAT, INIT, NAME = N'test_sql_DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
    END

    Do I need to add a coulumn into the Database in question?

    Sorry if my questions seem a bit basic but I've been thrust into the Admin role by picking a short straw, and am still trying to get to grips with some of the fundementals. I'm enjoying the learning experiece though Smile

    Niels
    Tuesday, April 17, 2007 9:57 AM
  • No need to apologise!

     

    It's my mistake, you need to enclose the database name in single quotes, e.g.

     

    DATABASEPROPERTYEX('test_sql_db', 'Updateability') ... etc.

    Tuesday, April 17, 2007 10:55 AM
  • Works like a charm - thanks for your help Smile
    Tuesday, April 17, 2007 11:56 AM