November 12, 2014 at 7:49 pm
Hi Guys,
Recently I have faced one DBA interview, below is the question they asked me.
" Does AlwaysON secondary replica support Full backup, if it is not why"?
I know AlwaysON secondary replicas support only copy_only and tlog backups, why they wont support full backup? any specific reason would help.
Thank you.
November 12, 2014 at 10:41 pm
Why do you think they don't support full backup (which I assume means full database backup)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2014 at 11:07 pm
I have tested in my environment with full database backup command on secondary replica, I got below error:
"Msg 3059, Level 16, State 1, Line 1
This BACKUP or RESTORE command is not supported on a database mirror or secondary replica.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally."
Thank you.
November 13, 2014 at 1:00 am
What was the command?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2014 at 3:00 am
AG secondaries support copy_only full backups.
See Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups) for reference.
The reason is that full backups reset the diff bitmap on the database, which is clearly not supported on a read-only database. With COPY_ONLY, the diff bitmap is not reset.
-- Gianluca Sartori
November 13, 2014 at 5:25 am
Thanks for your clarification :).
Excuse me if I am asking basic question, why clearing DCM Page(full DB backup and Diff backup) doesn't support for readonly databases?
Thanks.
November 13, 2014 at 6:52 am
Sathvik (11/13/2014)
why clearing DCM Page(full DB backup and Diff backup) doesn't support for readonly databases?
Because it's modifying the database, which is not allowed since it is read-only.
-- Gianluca Sartori
November 13, 2014 at 7:54 am
Sathvik (11/13/2014)
Thanks for your clarification :).Excuse me if I am asking basic question, why clearing DCM Page(full DB backup and Diff backup) doesn't support for readonly databases?
Thanks.
What would happen if the full backup were to increment the differential base lsn on a readable secondary, the primary would no longer!!
Also, as the database is read only, no changes are allowable, not even to the system catalogs
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 13, 2014 at 9:22 am
Hi All,
We are also in the process of migrating to Sql 2014 and setting up always on.
We are concerning about the normal full backup on Alwayson.
Correct me if I am wrong
So you are saying in 2014 also, it allows copy only backups but it doesn't allow normal fullbackups
November 13, 2014 at 9:31 am
ramana3327 (11/13/2014)
Hi All,We are also in the process of migrating to Sql 2014 and setting up always on.
We are concerning about the normal full backup on Alwayson.
Correct me if I am wrong
So you are saying in 2014 also, it allows copy only backups but it doesn't allow normal fullbackups
As detailed here copy_only backups are the only full backups allowed on active secondaries
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 13, 2014 at 11:32 am
Thanks you guys 😀
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply