May 4, 2016 at 3:18 am
Hi All,
I have a four node AAG with two nodes on the east coast of the US and two nodes on the West Coast. I have configured the daily full backup and hourly log backups to "Prefer Secondary" and exclude the west coast nodes (east coast is primary Data Centre.)
Because I can only perform "Copy Only" full backups of the database on the Secondary replica when I try to backup the log SQL thinks the database has not had a full backup and I get the error "BACKUP LOG cannot be performed because there is no current database backup". I understand I could get round this by failing over to the secondary and doing a FULL backup without Copy Only set but this seems clumsy and a pain as I have four nodes in this AAG and would have to do this four times.
Is there a way round this I am missing?
May 4, 2016 at 4:01 am
copy_only does not interfere with log backups, this option prevents the differential base lsn from being incremented and stops an intermediate full backup from breaking the differential backup chain, logs are unaffected.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 4, 2016 at 4:25 am
Thanks for your reply. I understand that Copy Only backups do not affect the LSN.
In order to take a log backup you must first have a full backup of the database, when the database is a replica in an AAG you can only do a Copy only backup. My log backup job keeps failing because there is no Full database backup.
I am asking if there is a way round this other than failing the database over to each individual node and doing a full backup without copy only selected.
May 4, 2016 at 4:53 am
A copy-only backup is a full backup, it should initialise the log chain without problems, as the only difference to a non-copy only is that the copy only doesn't reset the differential base.
Check that the full backup you took on the secondary (the one with copy-only) succeeded. If it did, try taking a normal full backup on the current primary replica (with no failovers) and see if that lets the log backup run.
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
May 4, 2016 at 6:18 am
Hi Gail
I just tested what you suggested and found the Copy only backup did not allow me to perform a log backup, as before it complained about not having a full backup. I guess this is because the Copy Only backup does NOT initialize the log chain. When I performed a full backup on the Primary without Copy only I am now able to backup the log on the Secondary Replica.
Thanks for your help.
May 4, 2016 at 6:28 am
is this the first ever full backup taken for this database?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 4, 2016 at 6:31 am
Yes the database has only been restored to this environment recently. Therefore the only Full backups ever taken have been done on the replica's with Copy Only specified.
May 4, 2016 at 6:56 am
aaron.chapman (5/4/2016)
I guess this is because the Copy Only backup does NOT initialize the log chain.
It should initialise the log chain, copy-only is on a full backup supposed to only differ in behaviour around differentials. Weird.
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
May 4, 2016 at 7:05 am
Strange. I will do some more tests with other db's on this environment. But pretty confident copy only didn't allow me to take a log backup.
May 4, 2016 at 8:23 am
It's not well documented by MS (or at all, maybe), but copy-only fulls will not allow log backups to be taken if they are the only full since the DB was switched to the full recovery model.
Someone asked Paul Randal about that at http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/, and his explanation was this:
It’s because any copy-only backup does not affect *anything* to do with the database, so the copy-only full backup doesn’t set the LSN field in the database metadata that is the pre-requisite for moving from pseudo-simple recovery model to full recovery model.
Cheers!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply