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

Stairway to Transaction Log Management in SQL Server, Level 3: Transaction Logs, Backup and Recovery Expand / Collapse
Author
Message
Posted Thursday, June 2, 2011 8:40 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Yesterday @ 8:21 AM
Points: 569, Visits: 1,038
Comments posted to this topic are about the item Stairway to Transaction Log Management in SQL Server, Level 3: Transaction Logs, Backup and Recovery
Post #1118784
Posted Wednesday, September 7, 2011 7:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
One correction on this - after switching from simple to full or bulk-logged, you can restart the log backup chain with a differential backup instead of a full backup, as long as a full backup has already been performed at some point in the past. A full backup is not required.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #1171081
Posted Wednesday, September 7, 2011 8:58 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 6:06 PM
Points: 429, Visits: 1,595
"Essentially, both full and differential backups only back up the data, although they do also back up enough of the transaction log to enable recovery of the backed up data, and reproduce any changes made while the backup was in progress."

Please correct me if I am wrong but it seems I read that it's impossible to say whether all changes made during backup are actually were backed up. Thanks
Post #1171495
Posted Wednesday, September 7, 2011 9:07 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
Correct - the only way to know whether a transaction is wholely contained within a data backup is if the LSN of the commit log record is within the range of LSNs backed up by the backup.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #1171497
Posted Wednesday, September 7, 2011 9:24 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 6:06 PM
Points: 429, Visits: 1,595
Thanks Paul-
that means there is actually way to confirm that particular transaction was backed up.
But still there is no warrantee that all changes were backed up.
Post #1171500
Posted Thursday, September 8, 2011 12:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 4:49 AM
Points: 3, Visits: 148
It says in the article that :
"Transaction log backups – makes a copy of all the log records inserted into the transaction log since the last transaction log backup (or database checkpoint, if working in SIMPLE recovery mode)."

as long as I know, there is no way to take transactional backup from a Database with simple recovery model.

although, it said in the beginning of the paragraph, that it's not applied when in simple recovery model,
is there a point to mentioning this afterward ?
Post #1171529
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse