0

I am running a SQL Server 2016 2-node Always On Availability Group, and I want to do some backups. I normally run this query to do my backup:

-- To permit log backups, before the full database backup, modify the database to use the full recovery model.  
USE master;  
ALTER DATABASE dbname
   SET RECOVERY FULL;  


BACKUP DATABASE dbname
   TO URL = 'https://bloblname.blob.core.windows.net/databasebackup/backup.bak'
   WITH CREDENTIAL = 'credentialname';

My question is, can I do this part on an Availability Group Primary Node without incurring downtime?

ALTER DATABASE dbname
   SET RECOVERY FULL;  

I am worried that if I run this command on the availability group primary it is going to somehow cause synchronization to stop, or perhaps have other effects that could be detrimental.

Another related question is - do you advise running these commands on the primary or the secondary?

Any help with this would be greatly appreciated.

3
  • When you say "I want to do some backups" you mean it's not one of the scheduled backups from your backup plan? Like you just want to take a backup before doing some other database maintenance and want to avoid messing with it without having a backup to restore from? If that's what you're willing to do, I can't see any benefit from changing the recovery mode to full before backing up the data.
    – Ronaldo
    Apr 8, 2020 at 20:58
  • @Ronaldo Yes, that was the thinking
    – A X
    Apr 8, 2020 at 23:34
  • Then I'd say there's no need to change the recovery mode to take the backup. If log backups are needed, they should be included on the backup plan and if that was the case, the database would already be set to full recovery mode. Also, there's no set database to simple recovery mode on your code sample, so if you were to change a database from simple to full recovery mode and leave it like that, chances are good that logs will grow without log backups, thus filling disk space and stopping one or more databases. Just an advice since you already have your question answered ;)
    – Ronaldo
    Apr 9, 2020 at 0:43

1 Answer 1

5

Availability groups require databases to be in full recovery mode (see Availability Database Prerequisites and Restrictions), so there should be no need to switch it to full prior to running a backup. The database should not be switched out of full recovery mode if it is in an availability group.

As to where to run the backup, on the primary or secondary, it is a matter of load distribution, and currency. If you don't want the load of the backup to run on the primary, then you'll need to run it on the secondary. If they are in asynchronous replication, the backup may not contain all of the latest data if some of the database replication is backed up in the log send queue.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.