Can we restore copy-only full backup and regular tlog backup?

Question

Can we restore copy-only full backup and regular tlog backup?

coolchaitu

SSChampion

Points: 10332
More actions
December 6, 2017 at 2:17 pm

#341923

Good Morning Experts
Can we restore copy-only full backup and regular tlog backup?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

coolchaitu - Wednesday, December 6, 2017 2:17 PM
Good Morning Experts
Can we restore copy-only full backup and regular tlog backup?

I am going to go out on a limb here and say no. You want to check Books Online to be sure.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 2

You should be able to. The copy only backup merely prevents resets for the diff map. A normal restore chain should apply.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

December 6, 2017 at 2:55 pm

#1971074

All you can do is try it and let us know.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

This doesn't really answer the question: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 5

Just tested on SQL 2016. Copyonly backup full + log from main db works fine.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Steve Jones - SSC Editor - Wednesday, December 6, 2017 3:12 PM
Just tested on SQL 2016. Copyonly backup full + log from main db works fine.

And that is what we needed. Thanks, Steve. I can't play with the databases I have access to at work.

barsuk SSChampion Points: 12280 More actions · Answer 7

question is a bit tricky as we wont be able to perform a tran log backup to begin with if copy_only full backup is being used as a base( and obviously it shouldn't and can't)
BACKUP LOG cannot be performed because there is no current database backup.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 8

That's true, but you always need a first full backup. There's no trickiness here. If your primary database hasn't had a full backup without copy only, you are asking for data loss in the event of some issue.

Your primary should have a backup chain of full and log backups that are used for DR purposes. You take a copy only backup to avoid disturbing your DR chain. The copy only would be used for dev refresh, testing, etc., and can also use the regular log backups with this full.

Jim Couper SSC Enthusiast Points: 179 More actions · Answer 9

If the database is part of an availability group and backups are performed on the secondary replica then you have no choice but to use copy only.