Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I need a quick clarification on copy-only backup vs full backup as it pertains to transaction log numbering and restore if required.
I have SQL Server 2012 setup with always on for multiple databases. The availability groups are setup with preferred backup on replica.
only copy-only and transaction log backups are possible on replica. Does this mean I will need to do a full backup on primary if a restore of the transaction log is required?
Thank you
only copy-only and transaction log backups are possible on replica
True.
Does this mean I will need to do a full backup on primary if a restore of the transaction log is required?
Short answer YES.
From BOL :
A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).
So to answer your question, a COPY_ONLY backup cannot be a part of a restore that involves T-log backups (doing a point-in-time restore). Its whole point is to have a backup set outside regular backup chain NOT impacting the restore sequence.
A full backup must be performed on primary database (cant be a copy_only backup).
Only T-log backup (as mentioned above) can be done on either primary or secondary with a CAVEAT that it wont mess up the LSNs on the PRIMARY i.e it will keep the LSNs consistent - regardless of where you take log backups in the availablity group.
Best is to check the sys.fn_hadr_backup_is_preferred_replica so the log backups will use the Availablity group backup preferences for Log backups.
Does this mean I will need to do a full backup on primary if a restore of the transaction log is required?
NO -- You can add t-log backups to a restore of a COPY_ONLY backup
You can restore transaction logs on top of a copy-only full backup--this means that you can use a copy-only backup from the secondary replica along with transaction logs, and accomplish point-in-time recovery.
However, if you are only performing copy-only backups on the secondary replica, you won't have a "real" backup to reset the differential bitmap on the primary replica. If differential backups are part of your recovery strategy, then you need to take full backups on primary. If you every want to use a differential backup, you would need to take a full backup on the primary replica to use as the differential base.
I have successfully tested using a copy-only backup and the log backups that span the copy-only up to the point in time desired. You do need to have all the log backups. So if you have multiple replicas that you are using to do the backups(a failover happened for instance), you need to make sure and keep track of them. In my testing I simply set things up so that all backups go to a central location. SQL maintains the log chain for log backups across all nodes in the AG. A nice article here...http://info.tricoresolutions.com/blog/understanding-backups-with-sql-server-alwayson-high-availability-mirrors
I had lots of confusion around the topic - In AG cluster restoring Log backup following with Copy - only full backup.
It is now working fine. We can use copy only backup from secondary replica:
USE [master]
RESTORE DATABASE [xxxxx_testDB] FROM
DISK = N'D:\Backups\FULL_COPY_ONLY\xxxxx_testDB_FULL_COPY_ONLY.bak'
WITH FILE = 1,
MOVE N'xxxxx_testDB' TO N'D:\testdb\xxxxx_testDB.mdf',
MOVE N'xxxxx_testDB_log' TO N'D:\testdb\xxxxx_testDB.ldf',
NORECOVERY
GO
RESTORE LOG xxxxx_testDB
FROM DISK = 'D:\Backups\FULL_COPY_ONLY\xxxxx_testDB_LOG_1.trn'
WITH NORECOVERY;
GO
RESTORE LOG xxxxx_testDB
FROM DISK = 'D:\Backups\FULL_COPY_ONLY\xxxxx_testDB_LOG_2.trn'
WITH NORECOVERY;
GO
RESTORE DATABASE xxxxx_testDB WITH RECOVERY
