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

  • Comments posted to this topic are about the item Stairway to Transaction Log Management in SQL Server, Level 3: Transaction Logs, Backup and Recovery

  • 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

  • "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

  • 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

  • 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.

  • 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 ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply