Agent Jobs

Backup Readable Secondaries In AlwaysOn

Question:
For AlwaysOn… Can you backup readable secondary replicas?

Answer:
Full Database Backups? NO
Transaction Log Backups? YES

Here’s what happens whenever you try to run Full Database Backups for readable secondaries in an SQL AlwaysOn environment. In this scenario we have Sharepoint 2013 databases residing in an SQL 2014 AlwaysOn environment. I’m basically running a full database backup to disk. Nothing crazy here.

I get these errors:
This BACKUP or RESTORE command is not supported on a database mirror or secondary replica.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Msg 3059, Level 16, State 1, Line 2
This BACKUP or RESTORE command is not supported on a database mirror or secondary replica.
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.
Msg 3059, Level 16, State 1, Line 3
This BACKUP or RESTORE command is not supported on a database mirror or secondary replica.
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
Msg 3059, Level 16, State 1, Line 4
This BACKUP or RESTORE command is not supported on a database mirror or secondary replica.
Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally.
Msg 3059, Level 16, State 1, Line 5
This BACKUP or RESTORE command is not supported on a database mirror or secondary replica.

Here’s what happens whenever you try to run transaction log backups. IT WORKS.

So there you have it.

Full Database Backups DO NOT WORK, however; Transaction Log Backups work perfectly normal.

If you need to write up a process to check if the database server is primary, then you can use something like this:

use master;
set nocount on

if exists(select is_local, role_desc from sys.dm_hadr_availability_replica_states where role = 1 and role_desc = 'PRIMARY') 
	begin
		print 'This server [' + upper(@@servername) + '] is the primary.' 
		backup database [MyDatabase] to disk = 'E:\MyPath\MyDatabase.bak'
	end

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s