What can be the benefits of copy_only backup

Question

What can be the benefits of copy_only backup

anujkumar.mca

SSChasing Mays

Points: 621
More actions
October 30, 2018 at 8:01 am

#376141

Dear Folks,
I want to understand what could be the benefits of copy_only backup except independent with the existing log chain

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

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

jasona.work SSC Guru Points: 50117 More actions · Answer 1

Whether a copy_only backup will be beneficial or not depends largely on your use case.

In my case, I use copy_only backups to refresh my QA databases for the developers, in part because I don't have control over the normal day-to-day backups and in part because it's much faster than going to the hassle of putting in a ticket and waiting for the people who do control the backups to get around to running the restore. The copy_only doesn't break the differential backup chain (we take 2 fulls a week, differentials the rest of the week, and TLogs every couple hours,) so I don't have to worry about screwing up our regular backups.

But, as I said at the start, whether you find them useful or not will depend on your use case for them. If I had control of the backups (beyond setting a schedule) I'd likely run my QA refreshes directly from whatever backup system I'd be using, rather than manual copy_only.

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 2

Refer to this healthy discussion on stackoverflow for more details.
https://dba.stackexchange.com/questions/45876/difference-between-full-backup-and-copy-only-full-backup
Important takeaway is "The only difference between full and full-copy-only is that full-copy-only does not break the differential chain. Neither of them breaks the log chain as neither of them truncates the log file."

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 3

A COPY_ONLY full backup does not affect the base for a differential backup and a COPY_ONLY transaction log backup does not affect the log backup sequence. I've probably not explained it very clearly but have a look at the link below:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server?view=sql-server-2017

Thanks

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

The biggest benefit is that the DBA team won't kill you for screwing up their backups. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RGP SSCarpal Tunnel Points: 4381 More actions · Answer 5

Copy only doesn't break or disturb the regular backup chain

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

I think the biggest benefit is that you can move a "copy only" file anywhere you want it, lose it, or delete it, and it won't affect the possible restores using the GUI's.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)