Post reply

backup

  • March 2, 2016 at 7:00 am

    #316483

    How sql server knows when there are any chages in database while taking the differnetial backup and log backup ?

  • March 2, 2016 at 7:11 am

    #1862380

    I copied and pasted those very words into my favourite search engine. The question was answered in the very first result.

    John

  • March 2, 2016 at 7:44 am

    #1862391

    Well a transaction log is just a backup of the transaction log, and so there's no need to know what's changed to do that.

    For differential backups, google: differential change map

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

  • March 2, 2016 at 8:58 am

    #1862434

    As others have said, the log backup is the easiest one to explain. Any completed transactions that are currently in the log are what get backed up. Those completed transactions that have been backed up then get removed from the log. The next time you run a backup of the log, it only backs up any new transactions that have been completed (removing those). There's really nothing for it to track there. It doesn't know what it backed up previously. It just knows that what's in the log has to be backed up.

    Gail gave you the other answer.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 2, 2016 at 9:09 am

    #1862443

    Grant Fritchey (3/2/2016)

    Any completed transactions that are currently in the log are what get backed up.

    Uncommitted also get backed up.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

  • March 2, 2016 at 9:27 am

    #1862455

    See Paul Randals post. Very good

    http://www.sqlskills.com/blogs/paul/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup/

  • March 2, 2016 at 9:44 am

    #1862464

    GilaMonster (3/2/2016)

    Grant Fritchey (3/2/2016)

    Any completed transactions that are currently in the log are what get backed up.

    Uncommitted also get backed up.

    I always forget that part of how it works. I need a new brain.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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