SQL AG Secondary Database Backup Throwing "not supported" error with COPY_ONLY

  • ganci.mark

    Hall of Fame

    Points: 3517

    Hello. I am using SQL Server 2016 with 2 Node AG.  There is no listener. On the Secondary node when I attempt to take backups

    using WITH COPY_ONLY I receive the following error message:

    Msg 3059, Level 16, State 1, Line 13

    This BACKUP or RESTORE command is not supported on a database mirror or secondary replica.

    Msg 3013, Level 16, State 1, Line 13

    BACKUP LOG is terminating abnormally.

    The Backup will work when I remove COPY_ONLY parm.  This seems like the reverse behavior of what I should expect.

    Is there something I am missing?

    Backup preferences are set to PRIMARY.

    Thanks for any help

    Mark G

     

     

     

  • Michael L John

    One Orange Chip

    Points: 25950

    This makes no sense. Are you sure you are connecting to the correct instance? With copy_only should work on either the primary or secondary.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • ganci.mark

    Hall of Fame

    Points: 3517

    Hi Michael.  I agree.

    I verified one more time.

    select sys.fn_hadr_is_primary_replica ( 'xxxxxx' )    -----> this returned   0

    (1 row(s) affected)

    Also the AG group under "Availability Groups" in the Object Explorer shows "Secondary".

    BACKUP LOG [xxxxxx] TO DISK ='F:\CRM\Backups\xxxxxx\yyyyyyy.bak' WITH COPY_ONLY

    This  Resulted in :

    Msg 3059, Level 16, State 1, Line 21

    This BACKUP or RESTORE command is not supported on a database mirror or secondary replica.

    Msg 3013, Level 16, State 1, Line 21

    BACKUP LOG is terminating abnormally.

    I can run the backup with out  COPY_ONLY and it works on secondary!  This is the exact opposite behavior I would expect.

    ON Primary I can run the backup both with and without COPY_ONLY.

    Thanks

    Mark

     

     

     

  • Sue_H

    SSC Guru

    Points: 90793

    Yeah it doesn't make sense. The only way I can see that happening is if the primary and secondary aren't communicating with each other when you execute the backup. Maybe check the synchronization state of the secondary.

    Sue

  • anthony.green

    SSC Guru

    Points: 112515

    BACKUP LOG WITH COPY_ONLY?

    I would seriously suggest not doing that, it won't mark the log as re-usable.  I guess they have fixed it in 2016 if your getting an error.  Last I tried a LOG with COPY_ONLY in an AG was in SQL 2014.  I inherited a 8 node, 4 AG setup, the logs where growing stupidly, tracked it down the old DBA had setup copy only log backups so the log is never marked as re-usable.

     

    Looking at the documentation, they have indeed fixed it

    BACKUP LOG supports only regular log backups (the COPY_ONLY option is not supported for log backups on secondary replicas).

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32407

    Yes, I remember it used to be possible but it always seemed like a terrible idea.

  • Sue_H

    SSC Guru

    Points: 90793

    Oh I missed it was the log duh...thanks for following up Anthony.

    Sue

  • Michael L John

    One Orange Chip

    Points: 25950

    COPY_ONLY does not work with log backups, I think.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • ganci.mark

    Hall of Fame

    Points: 3517

    Thank you all for the responses.

    We do regular TRN backups on the Primary, so no need to worry about log growth.

    I am able to SUCCESSFULLY  take a log backup on the PRIMARY using WITH COPY_ONLY.

    So it seems it is not supported on the SECONDARY.

    If I do  regular Non Copy log backups of SECONDARY am I messing up my log CHAIN for the PRIMARY?

    I will need to  check that out.

    Thanks

     

  • anthony.green

    SSC Guru

    Points: 112515

    Don't do COPY_ONLY on your logs full stop. Just don't.

    The only reason I would ever suggest a COPY_ONLY backup is if I wanted to sneak in a log for a restore without going through the log shipping job, but that is a very rare case.

    Other than that, never do a COPY_ONLY on a log, it doesn't mark the log as reusable and will not release the space of the log, so it will just grow and grow and grow until you run out of disk space or some other process comes and does a NON COPY ONLY log backup.

    The only time you should ever see COPY_ONLY in an AOAG backup routine is where your taking FULL backups on a SECONDARY.

    Other than that don't use COPY_ONLY anywhere

  • Johan Bijnens

    SSC Guru

    Points: 134304

    what edition are you using ? Std edtn  vs Ent edtn ?

     

    ( No backups on secondary replica with basic AGs ( = std edtn ))

    • This reply was modified 7 months, 2 weeks ago by  Johan Bijnens.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] but most of the time this is me

  • ganci.mark

    Hall of Fame

    Points: 3517

    Thanks Anthony.

    We do our Full and Transaction log backups already on the Primary. So we do not have to worry about the truncating the logs.

    We wanted to take additional backups (copies) on the secondary  without affecting the LOG CHAIN.  Again these are in addition to the normal backups on Primary.  And As I understand from the input from every one  COPY_ONLY does not work on Secondary anymore.

    So I tested  normal transaction log backups on the Secondary and that indeed does break the chain of logs backups on the Primary. Log Backups from both primary and secondary would need to be applied in order.

    Johan, this is SQL 2016 Enterprise SP1 ... .4604

    Thanks

    Mark

     

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32407

    Strictly speaking it doesn't break the chain of backups provided that you know where all your backup files are at any one time. It sounds a bit chaotic though; in the event of an emergency do you really want to be dancing around trying to figure out where you need to get your files from?

Viewing 13 posts - 1 through 13 (of 13 total)

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