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 «««910111213»»

Managing Transaction Logs Expand / Collapse
Author
Message
Posted Thursday, January 12, 2012 3:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:34 AM
Points: 1,430, Visits: 707
Echoing everyone else's comments - great article!

However, I was all prepared to query your statement in the article:

It is important to note that while a full or differential backup starts a log chain, full and differential backups don't break the log chain


My understanding was always that performing a Full backup would break the log chain, and if you wanted to restore to a given point using your log backups, you would have to restore from the last full backup. That's what I thought copy_only backups were introduced for, to ensure an ad-hoc full backup would not disrupt the log chain.

However, having now tested it against a 2008 R2 instance sure enough taking a full backup does not break the log chain, so I have learned something new today! Thank you!

But having said that, it begs the question what is the point of copy_only backups now?
Post #1234616
Posted Thursday, January 12, 2012 4:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:33 AM
Points: 41,572, Visits: 34,501
SQLPhil (1/12/2012)
But having said that, it begs the question what is the point of copy_only backups now?


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

You wouldn't be the first to argue with me about full backups breaking the log chain, it's an (unfortunately) commonly held belief. They've never broken the log chain though.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1234643
Posted Thursday, January 12, 2012 6:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:34 AM
Points: 1,430, Visits: 707
Thank you very much Gail for clarifying all of this (and for the excellent article on COPY_ONLY). You have certainly added a lot of clarity to my understanding of backups and restores.

Speaking amongst my fellow DBA colleagues we were all under the (misguided) understanding that full backups broke the log chain. In fact, even in some training courses we've been told that this has been the case. It'll be nice to have the opportunity to turn round and inform the instructor for once!
Post #1234715
Posted Thursday, January 12, 2012 6:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:33 AM
Points: 41,572, Visits: 34,501
SQLPhil (1/12/2012)
It'll be nice to have the opportunity to turn round and inform the instructor for once!




Just be polite if you do. Speaking as a occasional trainer and presenter, there's nothing worse than a student who thinks he's right and you're wrong and makes a public issue out of it.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1234750
Posted Thursday, January 12, 2012 6:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
GilaMonster (1/12/2012)
...Speaking as a occasional trainer and presenter..


And a darned good one too, may I say !




Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1234769
Posted Thursday, February 16, 2012 7:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 21, 2014 8:34 AM
Points: 1,937, Visits: 733
Great article, company I contract for are constantly changing the recovery model and shrinking the log then changing the RM back - have presented them with the URL for this, fingers crossed they will see sense!

qh


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1253121
Posted Saturday, May 12, 2012 10:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 8:27 AM
Points: 19, Visits: 112
Can we do point in time recovery in Bulk loged recovery model?
If yes then how?
Post #1299211
Posted Sunday, May 13, 2012 12:15 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 5:22 PM
Points: 36,016, Visits: 30,308
best_yunus (5/12/2012)
Can we do point in time recovery in Bulk loged recovery model?
If yes then how?


Yes but only if there were no minimally logged transactions in the final log file that you want to recover up to the point in time.


--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." -- 04 August 2013
(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 #1299227
Posted Sunday, May 13, 2012 1:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 8:27 AM
Points: 19, Visits: 112
Now i got...

Thanks
Post #1299287
Posted Monday, May 14, 2012 10:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:33 AM
Points: 41,572, Visits: 34,501
Take a read through this: http://www.sqlservercentral.com/articles/Recovery+models/89664/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1299733
« Prev Topic | Next Topic »

Add to briefcase «««910111213»»

Permissions Expand / Collapse