SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
coolchaitu
coolchaitu
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4391 Visits: 1390
Good Morning Experts
Can we restore copy-only full backup and regular tlog backup?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225970 Visits: 40423
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)

Group: Administrators
Points: 332045 Visits: 20117
You should be able to. The copy only backup merely prevents resets for the diff map. A normal restore chain should apply.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225970 Visits: 40423
All you can do is try it and let us know.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225970 Visits: 40423
This doesn't really answer the question: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)

Group: Administrators
Points: 332045 Visits: 20117
Just tested on SQL 2016. Copyonly backup full + log from main db works fine.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225970 Visits: 40423
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
barsuk
barsuk
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8181 Visits: 7224
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
Steve Jones
SSC Guru
SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)

Group: Administrators
Points: 332045 Visits: 20117
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jim Couper
Jim Couper
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 557
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search