Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

COPY_ONLY Backups Expand / Collapse
Author
Message
Posted Wednesday, September 30, 2009 9:31 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.


ATB

Charles Kincaid

Post #795822
Posted Wednesday, September 30, 2009 9:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 2,107, Visits: 5,405
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/
Post #795848
Posted Wednesday, September 30, 2009 9:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.

ATB

Charles Kincaid

Post #795859
Posted Wednesday, September 30, 2009 11:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:54 PM
Points: 67, Visits: 230
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?
Post #795899
Posted Wednesday, September 30, 2009 1:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 7:06 AM
Points: 143, Visits: 548
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
Post #795991
Posted Wednesday, September 30, 2009 3:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 30, 2013 10:11 AM
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!
Post #796044
Posted Wednesday, September 30, 2009 5:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 36,941, Visits: 31,443
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #796093
Posted Thursday, October 1, 2009 10:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.


ATB

Charles Kincaid

Post #796470
Posted Thursday, October 1, 2009 6:12 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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. :) 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/
Post #796702
Posted Thursday, October 1, 2009 6:48 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.


ATB

Charles Kincaid

Post #796709
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse