Backup Database Error SQL Server 2012: This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. BACKUP DATABASE is terminating abnormally

Microsoft SQL Server 2012 has introduced a new feature called AlwaysOn High Availability. There are numerous resources online to get information on AlwaysOn High Availability. So here i am not going in depth with features of this.

Will be coming up with some interesting post on this topic later. But for those you have already configured this feature, you might run into below error while running a backup command.

"This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. BACKUP DATABASE is terminating abnormally"

This is because AlwaysOn is configured to only allow backups on the primary replica.

So avoid this if you are running backup from Some SSIS Package, make sure you include below phrase in your backup command

sys.fn_hadr_backup_is_preferred_replica ( name ) = 1

So this will run backup on databases which are only Primary replica.

Technorati Tags: AlwaysOn High Availability,database mirror,secondary replica,primary replica,Microsoft SQL Server 2012

Hope this helps.

Logon failure: unknown user name or bad password

This is one of the very often faced error for job failure.

Exact error goes as follows:
Unable to start execution of step (reason: Error authenticating proxy , system error: Logon failure: unknown user name or bad password.). The step failed.

This happens when the secret word(password) you provided while creating a credential for a proxy has got expired. Exact scenario may be, you have used a domain account which enforces password expiration policy and you have changed you password. This happens because sql server resolves your changed password when you authentication mode is set to accept mixed mode, but the credentials which you have created does not accept this or resolve the change. Now you if you have to make this proxy work you need to delete the credential and again create it with new password and map to proxy. This sometime may again hit your jobs. So better way is to alter the credential and provide the new password. This can easily be achieved by below script by replacing the values according as per your needs:

Script:
USE [master]
GO
ALTER CREDENTIAL [ProxyCredentialsName] WITH IDENTITY = N'DOMAIN\ACCOUNTNAME', SECRET = N'NewPasswordchanged'
GO