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
February 18, 2020 at 9:22 pm
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/
February 18, 2020 at 9:54 pm
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
February 18, 2020 at 11:39 pm
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
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).
February 19, 2020 at 10:04 am
Yes, I remember it used to be possible but it always seemed like a terrible idea.
February 19, 2020 at 12:40 pm
Oh I missed it was the log duh...thanks for following up Anthony.
Sue
February 19, 2020 at 1:25 pm
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/
February 19, 2020 at 2:07 pm
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
February 19, 2020 at 2:26 pm
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
February 19, 2020 at 2:49 pm
what edition are you using ? Std edtn vs Ent edtn ?
( No backups on secondary replica with basic AGs ( = std edtn ))
Johan
Learn to play, play to learn !
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 but most of the time this is me
February 19, 2020 at 2:56 pm
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
February 19, 2020 at 4:00 pm
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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply