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


COPY_ONLY Backups


COPY_ONLY Backups

Author
Message
Charles Kincaid
Charles Kincaid
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4513 Visits: 2384
David you are lucky. I've had that too. Mostly this is due to the databases having a high read to write ratio. This increases the probability that a fault at any given moment won't have a pending transaction. Some of our customers get all the incoming orders between 6PM and 10PM local time. Order filling takes place between 11PM and 3AM. The rest of the time our database just sits there. I could walk in and power cycle the server at 10AM without much fear.

That is why I said "risk". There is always a data loss risk. Full recovery is just one step in mitigating those risks.

ATBCharles Kincaid
Adi Cohn
Adi Cohn
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13307 Visits: 6597
Charles Kincaid (9/30/2009)

Then too look at the other articles elsewhere on COPY_ONLY. You will see the stories of people who do full backup on the weekend and Log backups daily. Wednesday some developer takes a full backup at 8AM and th automatic Log backup fires at 11PM. The developer took the backup file away, used it and deleted it. Friday at 3PM the RAID controller faulted. Once the RAID is back online you need a restore. Um. Why does this now fail to restore all the backups? Was there anything of importance done on Thursday? Let's hope not.


If I understood you correctly then you claim that the database can be restored to it’s state at Tuesday 11:00:00 PM (The last log backup that was taken before the developer did a full backup on the database). If this is the case then I’m sorry, but I disagree with you. In my opinion you’ll be able to restore this database to the state that it was at Thursday 11:00:00 PM (the last log backup that was taken in your example). Since you are talking about full and log backups, then as long as you have a full backup and all the log backups that were done since that full backup, you can restore the database to the same state that it was when the last log backup was taken regardless of the number of full backups that were taken since the full backup that you have . Try the run the script that I wrote earlier that shows it and see for yourself.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Charles Kincaid
Charles Kincaid
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4513 Visits: 2384
I just did Adi. Thank you. Very surprising. And here I was patting myself on the back for having all my research down. Then again one of the things that fully expected was to get my tail flamed. Articles are often good forum starters.

ATBCharles Kincaid
David Walker-278941
David Walker-278941
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 231
I know that all of this is (supposedly) documented more fully in BOL, but the purpose of these articles is to help clarify the topics they write about. This one makes it harder to understand. The article *mentions* Copy_Only backups but it doesn't give one whit of a clue, not a hint, about what a Copy_Only backup actually IS. Yes, I can read BOL, but an article like this ought to MENTION some broad overview of what a Copy_Only backup IS and what it's intended to do, and how it differs from the other backups.

Copy_Only is mentioned in the middle of a discussion about the problems with log backups, but how are we supposed to guess what a Copy_Only backup is good for?
Divya Agrawal
Divya Agrawal
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1446 Visits: 604
Yes even i am confused on the post. It does not say anywhere in the entire article, what is the main purpose of Copy_only backups. It starts with the growth of log file, but what has Copy_only has to do with growth of log file.

What i have discovered as the purpose of Copy_only is maintaining the archive point. It does not become a halt in our daily cycle of backup process. Say you have taken a Full backup and then a log backup, it takes the log backup and truncates the log. But, with Copy_only used while taking log backups, it does not truncate the logs. So, it does not hampers the daily backup cycle. If anyone asks for a backup in between, we can directly give the backup using Copy_only.

--Divya
douglas.dawson
douglas.dawson
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 17
I thought the article was a little shy on details regarding the option itself. I'm a developer that often has to put on a DBA hat, as well. I understood the two scripts and the lead up, but I didn't see where it was explained exactly what COPY_ONLY does. I read the article twice and it's not very clear to me what problem is or how it was solved with COPY_ONLY.

In the comments, there were some examples and such that fleshed it out, but I think the article could use some of those details.

Thanks for taking the time to write it, though!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339995 Visits: 42625
Great introduction to Copy_Only, Charles. It's about time you wrote an article. ;-)

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Charles Kincaid
Charles Kincaid
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4513 Visits: 2384
Thanks Jeff.

The next one will be better though. It's another learning experience. Thanks to all the responders too. I take no exception to anything that was said. I'll improve.

ATBCharles Kincaid
timothyawiseman
timothyawiseman
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3416 Visits: 920
Charles Kincaid (9/30/2009)
Vliet is correct. So are many of the other points raised. Yet there was one thing missed. Yet we have seen the following many times:

(1) Set up a database in full recovery.
(2) Use it for a long time without ANY back up at all.
(3) Observe Log file size growth patterns during step 2.
(4) Take a full backup
(5) Continue to do 2 and 3 with no Log backup at all.
(6) Report on how long it takes to (a) reach the maximum Log file size in express, or (b) fill up your drive.

Then too look at the other articles elsewhere on COPY_ONLY. You will see the stories of people who do full backup on the weekend and Log backups daily. Wednesday some developer takes a full backup at 8AM and th automatic Log backup fires at 11PM. The developer took the backup file away, used it and deleted it. Friday at 3PM the RAID controller faulted. Once the RAID is back online you need a restore. Um. Why does this now fail to restore all the backups? Was there anything of importance done on Thursday? Let's hope not.

Microsoft introduced this for a reason. Yes even they get it right at times. Smile Even so you can tell that the engine folk and the tools folk don't talk to each other that much. Note that the 2005 SSMS does not know about COPY_ONLY. Supposed to be fixed in 2008.


Perhaps I am missing something. You should be able to restore it to 11PM on Thursday when the last log backup was taken, presuming of course you still have an unbroken chain of log backups back to the last full backup that you do have available.

Although I normally used Red Gate SQL Backup as an intermediate tool, I have actually been through a scenario somewhat similar to this and had no problem effecting the restore.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Charles Kincaid
Charles Kincaid
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4513 Visits: 2384
timothyawiseman (10/1/2009)
Perhaps I am missing something. You should be able to restore it to 11PM on Thursday when the last log backup was taken, presuming of course you still have an unbroken chain of log backups back to the last full backup that you do have available.

Although I normally used Red Gate SQL Backup as an intermediate tool, I have actually been through a scenario somewhat similar to this and had no problem effecting the restore.


You are right about the restores. I was lead astray on that aspect. Yet I have seen the log file growth thing happen many times.

ATBCharles Kincaid
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