Always On Backup Plans - Copy Only Backups

Question

Always On Backup Plans - Copy Only Backups

Newbie0809

SSChasing Mays

Points: 655
More actions
July 11, 2014 at 9:32 am

#309063

Hi,
We have just implemented a SQL 2012 always on environment. We have a primary and secondary server. I am confused about how to set up the backup plans. The application team was happy to tell me that in sql 2012 always on we can offload the backups to the secondary, thus reducing overhead on the primary server.
However, the secondary only supports copy only full backups. I am unsure how these would be useful in a disaster event? I could not apply any trx log backups on a copy only backup. This means I need to run my full backups on the primary server?
Any help would be appreciated.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Newbie0809 (7/11/2014)
Hi,
We have just implemented a SQL 2012 always on environment. We have a primary and secondary server. I am confused about how to set up the backup plans. The application team was happy to tell me that in sql 2012 always on we can offload the backups to the secondary, thus reducing overhead on the primary server.
However, the secondary only supports copy only full backups. I am unsure how these would be useful in a disaster event? I could not apply any trx log backups on a copy only backup. This means I need to run my full backups on the primary server?
Any help would be appreciated.

1) This isn't aimed at you personally, but it continues to amaze me how frequently companies pursue the VERY COMPLEX, finicky yet often CRITICALLY-IMPORTANT (because it is the foundation for HA/DR) "AlwaysOn" technologies without really understanding, planning and testing it!!! Clearly the entire IT team should have been involved BEFORE implementation so that when you implemented you would concurrently roll out an appropriate backup/ha/dr plan.

2) If you look here, you will see that BACKUP LOG IS supported on secondaries: http://msdn.microsoft.com/en-us/library/hh245119.aspx

3) Be sure you check out Ola.Hallengren.com's free and totally awesome maintenance suite since it is configured to be able to recognize AGs in the backup section.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Newbie0809 (7/11/2014)
I could not apply any trx log backups on a copy only backup. This means I need to run my full backups on the primary server?

You're tested that exact scenario and the restores failed?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Newbie0809 SSChasing Mays Points: 655 More actions · Answer 3

When using the GUI to restore a copy only full backup (from the secondary) and subsequent trx log backups, it fails complaining about a break in the LSN chain. That was expected. If I doing it using T-SQL, it is successful.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Newbie0809 (7/11/2014)
When using the GUI to restore a copy only full backup (from the secondary) and subsequent trx log backups, it fails complaining about a break in the LSN chain. That was expected. If I doing it using T-SQL, it is successful.

NEVER EVER use the GUI to do things like you just did (nor almost anything that does DDL, backups, DML, etc)!! Perhaps you can use the GUI and then SCRIPT it out to TSQL that you can review/approve/modify/learn from.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

So it does work. What's the problem then (and you probably shouldn't be using the GUI in a disaster situation, especially if it's throwing incorrect error messages)?

p.s. That error is not expected, because there's nothing about copy-only full backups that interfere with the log chain in any way.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

btw, you do realise that if you offload the backups onto the secondary it's considered to be active for the purposes of licensing?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Newbie0809 SSChasing Mays Points: 655 More actions · Answer 7

I was under the impression that a copy only backup would not allow any trx log backups to be applied, preventing a complete restore chain. However, after the test I see that it is possible, so I can schedule full (copy only) backups on the secondary along with trx log backups.

I am aware that using the secondary to offload backups will affect licensing, thanks.

I have been researching this issue and most of the articles I have seen have mentioned GUI, so I ran a test. Most of the articles I read stress that always on allows you to offload backups, but they not explain in detail the limitations and disadvantages.