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


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


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

Author
Message
Tony Davis
Tony Davis
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: Administrators
Points: 2087 Visits: 1171
Comments posted to this topic are about the item Stairway to Transaction Log Management in SQL Server, Level 3: Transaction Logs, Backup and Recovery
Paul Randal
Paul Randal
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15640 Visits: 1736
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
Yuri55
Yuri55
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3886 Visits: 2109
"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
Paul Randal
Paul Randal
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15640 Visits: 1736
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
Yuri55
Yuri55
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3886 Visits: 2109
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.
Sina Hassanpour
Sina Hassanpour
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 203
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 ?
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