Help with Error 850 Syntax error:

ColmKColmK Posts: 1 New member
in SQL Backup
Hi

I used Redgate Backup Pro to create a full and differential backups, the full is completing and the differentials are failing with the below error message
Syntax error: 'SECONDARY_REPLICA_COPY_ONLY' after ','

I grabbed the scripts from the last page of the UI....(I've replaced the list of databases and location)

The below full backup completes successfully
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [My list of databases] TO DISK = ''\\My Backup location\<AUTO>.sqb'' WITH ERASEFILES_PRIMARY = 14, CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, THREADCOUNT = 7, CHECK_PREFERRED_AG_REPLICA, SECONDARY_REPLICA_COPY_ONLY"'

The differential below gives the syntax error
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [My list of databases] TO DISK = ''\\My Backup location\<AUTO>.sqb'' WITH ERASEFILES_PRIMARY = 14, CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, THREADCOUNT = 7, DIFFERENTIAL, CHECK_PREFERRED_AG_REPLICA, SECONDARY_REPLICA_COPY_ONLY"'

The only difference being ......THREADCOUNT = 7, DIFFERENTIAL, C.........

I cant see what the extended SP 'master..sqlbackup' is doing, is there a bug with the string being passed in?


Can someone tell me why this is happening and offer a solution?

Thanks







Tagged:
· Share on Twitter

Answers

  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    edited March 10, 2021 4:38PM
    Hi, thank you for your forum post.

    Its the use of the SECONDARY_REPLICA_COPY_ONLY keyword in the Differential backup is the problem here.  The keyword can only be used for Full backups.

    Differential backup aren't supported on secondary replicas hence the syntax error in SQL Backup.  See this Microsoft document, the Backup Types Supported on Secondary Replicas section:

    • 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.
    • Differential backups aren't supported on secondary replicas.

    I hope the above answers your question.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
Sign In or Register to comment.