Can we use Differential Backups in Always ON Availability Groups

Hi Friends,

As you know in Availability Groups the default option is prefer secondary and if you start using Ola Scripts you need to add copy_only to offload backups. I explained everything  how to use Ola Scripts with both Availability Groups and Distributed Availability Groups in the below links

Now my question is say if we start using Differential backups it will run only on Primary server.

And as we are offloading backups we will have copy only full backups and log backups on Secondary servers.

Say for some reason the database needs to be restored and as per our logic we will go with restoration of 

1) copy only full backup
2) Latest Differential
3) Remaining Logs

But Wait if we try to restore Differential backup you will get the below error

Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

So my question is what will we do in this case? Of course we have alternative of restoring copy only full backups followed by log backups. However if for some reason if any log backups got deleted you are gone. 

Also if you have bigger databases say 50 TB in Always ON and say if someone asks you to take backup before some activity usually we will go with differential just in case if something goes wrong we think we can make use of it but don't do that if your database is part of ALWAYS ON

This happens even for a normal Database where we can't restore the differential backup with copy only full backup as base. I am specifically targeting Availability Groups because we will offload backups as the default option is prefer secondary where as for standalone databases we will not take copy only full backups. Hope my point makes sense.


Unknown said…
I'm sure it isn't silly post!! I got a question is that behaviour observed only when you are using Ola script to backup databases?? because I have seen same kind of error in one of my prod servers though databases are being backed up using maintenance plans.
Thanks you and your posts have abundance information!!
Thanks a lot for your reply. This does comes in to picture even for normal Databases.