Exam DP-300 topic 6 question 27 discussion

Actual exam question from Microsoft's DP-300

Question #: 27
Topic #: 6

You have an Always On availability group deployed to Azure virtual machines. The availability group contains a database named DB1 and has two nodes named
SQL1 and SQL2. SQL1 is the primary replica.
You need to initiate a full backup of DB1 on SQL2.
Which statement should you run?

A. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/mycontainer/DB1.bak' with (Differential, STATS=5, COMPRESSION);
B. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/mycontainer/DB1.bak' with (COPY_ONLY, STATS=5, COMPRESSION);
C. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/mycontainer/DB1.bak' with (File_Snapshot, STATS=5, COMPRESSION);
D. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/mycontainer/DB1.bak' with (NoInit, STATS=5, COMPRESSION);

Show Suggested Answer

Suggested Answer: B 🗳️
BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it's executed on secondary replicas. Copy-only backups don't impact the log chain or clear the differential bitmap.
Incorrect Answers:
A: Differential backups are not supported on secondary replicas. The software displays this error because the secondary replicas support copy-only database backups.
Reference:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-backup-on-secondary-replicas-always-on-availability-groups

by Ziggybooboo at Feb. 17, 2021, 6:50 p.m.

Comments

Submit Cancel

Raffer

Highly Voted 2 years, 1 month ago

The question specifies that this is a backup on SQL2, which is a Secondary replica. Backups on Secondary replicas can only be of the type COPY_ONLY.

upvoted 13 times

...

Ciupaz

Most Recent 6 months, 3 weeks ago

You should use the COPY_ONLY option. This causes the backup to run as a copy-only backup so the backup does not affect on your normal backup schedule. This lets you create a backup that is independent of your regularly scheduled, conventional backups.

upvoted 2 times

...

lukelin08

7 months, 3 weeks ago

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-backup-on-secondary-replicas-always-on-availability-groups?view=sql-server-ver16

upvoted 1 times

...

Chunchi

1 year, 3 months ago

Selected Answer: B

Secondary replica database can only be backup COPY_ONLY.

upvoted 2 times

...

Ziggybooboo

2 years, 2 months ago

Thought copy_only is only relevenat to SQL DB/MI not IaaS servers

upvoted 4 times

Lewy

2 years, 1 month ago

It is valid on SQL Server , Managed instance https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server?view=sql-server-ver15