locked
BACKUP failed to complete the command BACKUP LOG RRS feed

  • Question

  • Getting BACKUP failed to complete the command BACKUP LOG qoemetrics for our Lync Server 2013 SQL 2012 mirror. No backups defined on mirror. Any ideas?
    Tuesday, January 21, 2014 4:30 PM

Answers

  • You cannot backup mirrored databases or the log file. 

    If you are using a maintenance plan, you can check the box "only online databases". 

    In TSQL, you would need to add logic to only backup "online" databases.  However, I would strongly suggest using Ola's backup scripts, which already have that logic built into the script.  http://ola.hallengren.com/

    • Marked as answer by Sofiya Li Thursday, January 30, 2014 9:44 AM
    Wednesday, January 22, 2014 1:44 PM
  • On the mirror server, create a backup maintenance plan for all databases and select the option "Ignore databases where state is not online".

    • Marked as answer by Sofiya Li Thursday, January 30, 2014 9:44 AM
    Wednesday, January 22, 2014 2:01 PM

All replies

  • SQL Server 2012 database mirroring does not support running backups on the mirrored database. You can only run backups on the principal database. Are you aware of any backup commands being executed against your mirror database outside of the database maintenance plans?

    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course


    • Proposed as answer by Tom Phillips Tuesday, January 21, 2014 8:43 PM
    Tuesday, January 21, 2014 8:42 PM
  • Thanks for your reply,

    I am aware that you cannot backup the mirror databases. This is a new SQL Server with no maintenance plans and I have not setup anything that would run external jobs.

    I can open a Premiere support case just wanted to check the forums to see if anyone else had seen this before.

    Wednesday, January 22, 2014 1:02 PM
  • Getting BACKUP failed to complete the command BACKUP LOG qoemetrics for our Lync Server 2013 SQL 2012 mirror. No backups defined on mirror. Any ideas?

    Hello,

    Can you paste complete error message.Can you post your backup TSQL as well


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, January 22, 2014 1:22 PM
  • This is happening on all my Mirror databases. TSQL from The primary node to follow shortly.

    Log Name:      Application
    Source:        MSSQLSERVER
    Date:          1/21/2014 11:32:45 PM
    Event ID:      3041
    Task Category: Backup
    Level:         Error
    Keywords:      Classic
    User:          SYSTEM
    Computer:      wme-lync13sql2.idexxi.com
    Description:
    BACKUP failed to complete the command BACKUP LOG qoemetrics. Check the backup application log for detailed messages.
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="MSSQLSERVER" />
        <EventID Qualifiers="49152">3041</EventID>
        <Level>2</Level>
        <Task>6</Task>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2014-01-22T04:32:45.000000000Z" />
        <EventRecordID>41935</EventRecordID>
        <Channel>Application</Channel>
        <Computer>wme-lync13sql2.idexxi.com</Computer>
        <Security UserID="S-1-5-18" />
      </System>
      <EventData>
        <Data>BACKUP LOG qoemetrics</Data>
        <Binary>E10B0000100000000F00000057004D0045002D004C0059004E00430031003300530051004C0032000000070000006D00610073007400650072000000</Binary>
      </EventData>
    </Event>


    Wednesday, January 22, 2014 1:38 PM
  • You cannot backup mirrored databases or the log file. 

    If you are using a maintenance plan, you can check the box "only online databases". 

    In TSQL, you would need to add logic to only backup "online" databases.  However, I would strongly suggest using Ola's backup scripts, which already have that logic built into the script.  http://ola.hallengren.com/

    • Marked as answer by Sofiya Li Thursday, January 30, 2014 9:44 AM
    Wednesday, January 22, 2014 1:44 PM
  • I have no backup jobs running on the mirror SQL node. I have a backup plan on the primary SQL server to backup User databases.
    Wednesday, January 22, 2014 1:54 PM
  • BACKUP DATABASE [rtcxds] TO  DISK = N'B:\Backups\rtcxds_backup_2014_01_22_085201_7525216.bak' WITH NOFORMAT, NOINIT,  NAME = N'rtcxds_backup_2014_01_22_085201_7525216', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [rtcshared] TO  DISK = N'B:\Backups\rtcshared_backup_2014_01_22_085201_7575240.bak' WITH NOFORMAT, NOINIT,  NAME = N'rtcshared_backup_2014_01_22_085201_7575240', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [rtcab] TO  DISK = N'B:\Backups\rtcab_backup_2014_01_22_085201_7625117.bak' WITH NOFORMAT, NOINIT,  NAME = N'rtcab_backup_2014_01_22_085201_7625117', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [rgsconfig] TO  DISK = N'B:\Backups\rgsconfig_backup_2014_01_22_085201_7665100.bak' WITH NOFORMAT, NOINIT,  NAME = N'rgsconfig_backup_2014_01_22_085201_7665100', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [rgsdyn] TO  DISK = N'B:\Backups\rgsdyn_backup_2014_01_22_085201_8005299.bak' WITH NOFORMAT, NOINIT,  NAME = N'rgsdyn_backup_2014_01_22_085201_8005299', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [cpsdyn] TO  DISK = N'B:\Backups\cpsdyn_backup_2014_01_22_085201_8045296.bak' WITH NOFORMAT, NOINIT,  NAME = N'cpsdyn_backup_2014_01_22_085201_8045296', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [xds] TO  DISK = N'B:\Backups\xds_backup_2014_01_22_085201_8095321.bak' WITH NOFORMAT, NOINIT,  NAME = N'xds_backup_2014_01_22_085201_8095321', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [lis] TO  DISK = N'B:\Backups\lis_backup_2014_01_22_085201_8135340.bak' WITH NOFORMAT, NOINIT,  NAME = N'lis_backup_2014_01_22_085201_8135340', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [mgc] TO  DISK = N'B:\Backups\mgc_backup_2014_01_22_085201_8185450.bak' WITH NOFORMAT, NOINIT,  NAME = N'mgc_backup_2014_01_22_085201_8185450', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [LcsCDR] TO  DISK = N'B:\Backups\LcsCDR_backup_2014_01_22_085201_8225412.bak' WITH NOFORMAT, NOINIT,  NAME = N'LcsCDR_backup_2014_01_22_085201_8225412', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [QoEMetrics] TO  DISK = N'B:\Backups\QoEMetrics_backup_2014_01_22_085201_8265396.bak' WITH NOFORMAT, NOINIT,  NAME = N'QoEMetrics_backup_2014_01_22_085201_8265396', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [ReportServer] TO  DISK = N'B:\Backups\ReportServer_backup_2014_01_22_085201_8315433.bak' WITH NOFORMAT, NOINIT,  NAME = N'ReportServer_backup_2014_01_22_085201_8315433', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [ReportServerTempDB] TO  DISK = N'B:\Backups\ReportServerTempDB_backup_2014_01_22_085201_8355531.bak' WITH NOFORMAT, NOINIT,  NAME = N'ReportServerTempDB_backup_2014_01_22_085201_8355531', SKIP, REWIND, NOUNLOAD,  STATS = 10
    Wednesday, January 22, 2014 1:54 PM
  • On the mirror server, create a backup maintenance plan for all databases and select the option "Ignore databases where state is not online".

    • Marked as answer by Sofiya Li Thursday, January 30, 2014 9:44 AM
    Wednesday, January 22, 2014 2:01 PM
  • Thanks I will give that a shot.
    Wednesday, January 22, 2014 2:06 PM
  • Another thing to consider when you receive this error message...

    If you're using maintenance plans, make sure you have manually performed a full backup of said databases beforehand.


    There is always a way...

    Friday, October 3, 2014 8:08 PM