locked
Offload backups on Secondary Replica RRS feed

  • Question

  • Hi Team,

    Can i perform full and differential backups on Primary Replica and Copy-Only Log Backups on Secondary Replica?

    I am using SQL Server 2016 ENT (Primary+1Secondary)

    Monday, May 18, 2020 7:19 PM

Answers

  • Hi VijayKSQL,

    It is not "no need",it is "no support". Please reference: SupportedBuTypes

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

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by VijayKSQL Thursday, May 28, 2020 7:07 PM
    Friday, May 22, 2020 7:29 AM

All replies

  • why? i am doing daily full on both primary and secondary
    Monday, May 18, 2020 7:27 PM
  • Sorry

    weekly full backup and daily diff backups and every 15 mins log

    my plan is to avoid log backups on Primary. so that i can minimize the load on Primary.

    Monday, May 18, 2020 9:53 PM
  • Hi VijayKSQL,

    >Can i perform full and differential backups on Primary Replica and Copy-Only Log Backups on Secondary Replica?

    No.You can't.Because the COPY_ONLY option is not supported for log backups on secondary replicas, you can choose to have one regular log backup on secondary replica.Please refrence Backup Types Supported on Secondary Replicas

    Note:You can perform log backup and full database backup on the secondary replica. However, full backup (BACKUP DATABASE) only supports "copy-only backup" of databases,"copy-only backup" of files or file groups. Copy-only backup does not affect the log chain, nor does it clear the differential backup bitmap. The secondary replica does not support differential backups.

    This may be helpful: sql-server-always-on-availability-group-log-backup-on-secondary-replicasalwayson-backup-and-other-tips

    Best Regards.

    yuxi




    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 19, 2020 2:12 AM
  • Hi VijayKSQL,

    Is the reply helpful?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 20, 2020 1:27 AM
  • Hi Yuxi,

    Getting little confusion.

    Please let me explain:

    I am planning to affload backup to secondary in order to reduce the load on Primary.

    My plan is to take Daily Diff,Weekly Full and every 15 mins log.

    Now please suggest me how to configure?

    Wednesday, May 20, 2020 5:58 PM
  • Hi VijayKSQL,

    Maybe my reply is not clear.

    >Can i perform full and differential backups on Primary Replica and Copy-Only Log Backups on Secondary Replica?

    You can full and differential backups on Primary Replica and Regular Log Backups on Secondary Replica.

    And for your frequent backups, suggest you make one plan to delete the old backup files.
    And this may be helpful:sql-server-backup , create-daily-database-backups-with-unique-names-in-sql-server,

    how-to-delete-old-database-backup-files-automatically-in-sql-server

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, May 21, 2020 7:14 AM
  • You mean,

    secondaries support regular log backup and no need to take copy-only log backups am i correct?


    Friday, May 22, 2020 6:37 AM
  • Hi VijayKSQL,

    It is not "no need",it is "no support". Please reference: SupportedBuTypes

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

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by VijayKSQL Thursday, May 28, 2020 7:07 PM
    Friday, May 22, 2020 7:29 AM