Does specifying COPY_ONLY make a difference?

Question

Post reply

Does specifying COPY_ONLY make a difference?

derek.colley

SSCrazy Eights

Points: 8040
More actions
December 1, 2011 at 3:33 am

#247726

Hi
I'm doing some backups to be restored to a different server as new DBs and it got me thinking...
Is there any reason, other than the one pointed out by Microsoft (that a copy-only backup cannot be used as a differential base) why I would need to use COPY_ONLY? As in my experience a perfectly valid restore from a non-COPY_ONLY backup works fine.
Or are there other considerations / best practices?
Link to BACKUP(T-SQL) here -> http://msdn.microsoft.com/en-us/library/ms186865.aspx
Derek.
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:
Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

COPY_ONLY does not break the log chain.

So if you have log shipping configured, you can use COPY_ONLY option to take a full database backup of testing, etc.

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Suresh B. (12/1/2011)
COPY_ONLY does not break the log chain.
So if you have log shipping configured, you can use COPY_ONLY option to take a full database backup of testing, etc.

COPY_ONLY is useful only when you're working with differential backups.

AFAIK, Log Shipping does not use differential backups, so COPY_ONLY has no place there.

-- Gianluca Sartori

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 3

I thought so. Thanks for your reply!

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

george sibbald SSC Guru Points: 104210 More actions · Answer 4

Suresh B. (12/1/2011)
COPY_ONLY does not break the log chain.
So if you have log shipping configured, you can use COPY_ONLY option to take a full database backup of testing, etc.

full backups without copy_only do not break the log chain either. You can take as many full backups as you like when doing log shipping. Copy_only with full backups is only relevant to differentials

---------------------------------------------------------------------

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

Suresh B. (12/1/2011)
COPY_ONLY does not break the log chain.

Full backups do not EVER break the log chain, regardless of whether they're taken with copy_only or not..

So if you have log shipping configured, you can use COPY_ONLY option to take a full database backup of testing, etc.

You can, but you don't have to.

http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/

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