Skip to main content
Solved

SQL AG backup target (Secondary)


Forum|alt.badge.img+14

Hi all

I have very large SQL DB on a client site. Due to it’s size the client is also a Media Agent that backs itself up and then replicates a second copy of the backup data to the secondary SQL node in the AG.
What we’d like to do is use the Secondary node to backup the SQL AG DB from itself which would allow the primary node to not be accessed.
From what I can see this is not possible. You can only run a backup copy/TLogs on the Secondary node.

 

I just want to confirm there is no way to satisfy this client request?

Best answer by Edd Rimmer

Hi Mauro,

You’re correct in that you can only run a copy-only data backup on replicas.

Are you running running differential backups? If not, then the fact you would only have a copy-only backup may not matter here → https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server?redirectedfrom=MSDN&view=sql-server-2017

Regards
Edd

View original
Did this answer your question?

4 replies

Edd Rimmer
Vaulter
Forum|alt.badge.img+7
  • Vaulter
  • 58 replies
  • Answer
  • May 6, 2021

Hi Mauro,

You’re correct in that you can only run a copy-only data backup on replicas.

Are you running running differential backups? If not, then the fact you would only have a copy-only backup may not matter here → https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server?redirectedfrom=MSDN&view=sql-server-2017

Regards
Edd


Forum|alt.badge.img+14
  • Author
  • Byte
  • 101 replies
  • May 6, 2021

Thank you Edd. I’ve fed this back to the customer.

Copy Only wouldn’t work as we run Differential and Tlog backups daily. 
 

Thanks again!


Edd Rimmer
Vaulter
Forum|alt.badge.img+7
  • Vaulter
  • 58 replies
  • May 6, 2021

No problem! Please remember that a copy only backup cannot be used as a base for differentials.


Forum|alt.badge.img+14
  • Author
  • Byte
  • 101 replies
  • May 6, 2021

Thanks, that’s noted.:thumbsup:


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings