Stairway to Transaction Log Management in SQL Server, Level 3: Transaction Logs, Backup and Recovery

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Tony Davis » Stairway to Transaction Log Management in SQL...

Stairway to Transaction Log Management in SQL Server, Level 3: Transaction Logs, Backup and Recovery

Rate Topic

Display Mode

Topic Options

Author

Message

Tony Davis

Posted Thursday, June 02, 2011 8:40 AM

Mr or Mrs. 500

Group: Administrators
Last Login: Wednesday, May 08, 2013 10:31 AM
Points: 511, Visits: 945

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

Paul Randal

Posted Wednesday, September 07, 2011 7:52 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905, Visits: 1,601

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

Yuri55

Posted Wednesday, September 07, 2011 8:58 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 20, 2013 6:39 PM
Points: 400, Visits: 1,432

"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

Paul Randal

Posted Wednesday, September 07, 2011 9:07 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905, Visits: 1,601

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

Yuri55

Posted Wednesday, September 07, 2011 9:24 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 20, 2013 6:39 PM
Points: 400, Visits: 1,432

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

sina_hs

Posted Thursday, September 08, 2011 12:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:02 PM
Points: 3, Visits: 97

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 »

Permissions