1

I am trying to run a LOG backup on the primary node of an Always ON SQL Server 2012 environment. The secondary server is only used for replication. no read or other access permitted. (licensing of microsoft EE).

When I do a full backup using Ola Hallengren scripts, all goes well. A full backup is placed in the location I need. But when I run the LOG backup, it does not carry out the backup at all. Testing a Differential backup also went fine. Doing a LOG backup manually, without Ola's script it does work too.

The backup I try is


USE [master]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[DatabaseBackup]
        @Databases = N'TESTAlwayON',
        @Directory = N'c:\backup',
        @BackupType = N'LOG'

SELECT  'Return Value' = @return_value

GO

The return value is 0, and the message shows all (default) values back, but not the actual backup statement, like it does when doing a FULL backup.

Any thoughts, what I do wrong?

Jimmy

UPDATE : Fixed. I ran the command

ALTER AVAILABILITY GROUP AG1 SET (AUTOMATED_BACKUP_PREFERENCE = PRIMARY)

And now it keeps the setting on primary and my log backups are running fine.

Thanks for the push in the correct direction.

  • 1
    What are the settings on the availability group's backup preferences? – Tony Hinkle Jun 3 at 13:19
  • Log to the command log table (@LogToTable='Y') and take a look at dbo.CommandLog – Jonathan Fite Jun 3 at 13:40
  • Thanks Tony, The backup preference is set to prefer secondary, but if I changed that to primary only, it jumps back to prefer secondary. If I shut down the secondary, the log backup is successful, therefor I think I am on the right track now. – Jimmy-one Jun 4 at 9:57
  • Hi Jonathan, the log to table was not filled in with new statements because the backup statement never went through if I used the backup LOG. But it probably has to do with the prefer secondary part. I now need to get that sorted. – Jimmy-one Jun 4 at 9:59
0

Your availability group is set to prefer backups on the secondary, and by default Ola's script is going to only run log backups on the node that is preferred.

Use the @OverrideBackupPreference='Y' parameter in the backup command to override that setting so that it will take the log backup on the node where the script runs regardless of the backup preference setting of the availability group.

  • Thanks Tony, I already expected that a parameter should be there for the use of Ola's script, but looked over it. – Jimmy-one Jun 7 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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