January 30, 2014 at 3:01 pm
Hello all-
I was hoping someone can shed some knowledge on Always On for me when it comes to back up on the secondary.
The four options are:
1. Only on the primary replica
2. On secondary replicas
3. Only on secondary replicas
4. No preference
I am testing always on and can't figure out how this part works. Here's what I did:
- Chose option 2 (On secondary replicas)
- Tried to take a manual full backup on the secondary
- Receive an error that type backup isn't possible I can only take t-log backups on the secondaries
So I chose option 4 instead and same issue.
Can someone please help me understand how these options work? Seems like no matter which backup option I choose it will only let me do full\diff's on whichever server is considered the primary.
Thank you for your time.
January 30, 2014 at 5:28 pm
DBANoobie (1/30/2014)
Hello all-I was hoping someone can shed some knowledge on Always On for me when it comes to back up on the secondary.
The four options are:
1. Only on the primary replica
2. On secondary replicas
3. Only on secondary replicas
4. No preference
I am testing always on and can't figure out how this part works. Here's what I did:
- Chose option 2 (On secondary replicas)
- Tried to take a manual full backup on the secondary
- Receive an error that type backup isn't possible I can only take t-log backups on the secondaries
So I chose option 4 instead and same issue.
Can someone please help me understand how these options work? Seems like no matter which backup option I choose it will only let me do full\diff's on whichever server is considered the primary.
Thank you for your time.
Read Books Online (a WONDERFUL resource!!). In this section: Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups) it states:
Backup Types Supported on Secondary Replicas
--------------------------------------------------------------------------------
• BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.
Differential backups are not supported on secondary replicas.
• BACKUP LOG supports only regular log backups (the COPY_ONLY option is not supported for log backups on secondary replicas).
A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 3, 2014 at 4:39 pm
Wow thank you so much for clarifying that with me sir, it is much appreciative.
February 4, 2014 at 3:50 pm
And you cannot use Differential backups on secondaries.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply