Solved

SQL AG backup target (Secondary)


Userlevel 4
Badge +12

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?

icon

Best answer by Edd Rimmer 6 May 2021, 09:44

View original

4 replies

Userlevel 4
Badge +12

Thanks, that’s noted.:thumbsup:

Userlevel 4
Badge +7

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

Userlevel 4
Badge +12

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!

Userlevel 4
Badge +7

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

Reply