backup background process

  • when backup is taken can anyone tell me as to what exactly happens in the background

  • Why do you want to know? What problem are you investigating?

    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
  • newbie wanting to understand sql

  • Well, at a high level, all used data pages read from the data file and written into the backup file. That's the core. It's a bit of a simplification, but good enough.

    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
  • What means "used pages"? Can you please clarify. Thanks

  • Yuri55 (10/14/2012)


    What means "used pages"? Can you please clarify. Thanks

    used means the pages which contain data .

    refer this link http://msdn.microsoft.com/en-us/library/ms175477(v=sql.90).aspx

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (10/15/2012)


    Yuri55 (10/14/2012)


    What means "used pages"? Can you please clarify. Thanks

    used means the pages which contain data .

    refer this link http://msdn.microsoft.com/en-us/library/ms175477(v=sql.90).aspx

    Make sense, thanks

    What does not make sense though (probably I am missing something :-))- let say you backup DB1 with 50% Available free space (from Shrink DB task you can see this number). Then you restore DB2 from this backup and I guess (saw it many times) that DB1 and DB2 have same size (not 50% less I mean). Why is this- because pages were not completely free on DB1 and therefore were backed up? Thanks

  • Because restoring a backup always recreates the database exactly as it was at the time the backup was taken.

    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
  • Sorry Gail but IMO it does not sound logical- if during backup only used (with data) pages are written to backup file then restored DB should have only such pages (i.e. with data only and no empty pages), i.e. can be different from original one? Or am I missing somethng? Thanks

  • The backup file (the .bak) contains only the pages (actually extents) that are allocated to objects. There's no point in backing up empty space.

    The restored database must however be exactly the same as the database that was backed up. If the source database had 50% free space, so will the restored DB. How would SQL tell cases where that 50% free space is desired and required from cases where is is not? It can't just assume that the free space can be removed (and, infact doing so could be a huge problem, it would essentially be doing a shrink after every restore, horrible)

    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
  • Make sense- thanks. If you do not mind 1 more question- regarding TrLog- same story or not? I mean original DB has 90% free log (let say after huge index rebuild and no shrinking issued) Seems to me there is no reason to restore same huge Trlog but only active part of it (i.e. 10%). Or am I wrong? Thanks

  • Same story and same reason.

    How is SQL to know that the 90% free space in the log is necessary or not? Let's say the log reaches 20GB used every night at 7pm but by the time the backup runs whatever job that was has finished and the log is using maybe 50MB. If a restore of that shrunk the log to 50MB then there would be either unpleasant surprises the next night at 7pm (log growth is resource-intensive) or the DBA would have to remember to grow the log after any restore.

    There's one time SQL does shrink the log by itself (reverting from a snapshot), and it's considered a huge bug/flaw.

    Oh, and to do what you want, the restore would have to do huge amounts of work that it does not currently do, reading pages and allocation structures, changing page headers, updating allocation pages, index next, previous, parent and child pointers and a ton more. Would make restores significantly slower than they are now, which is really not a good thing.

    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
  • To resume- restored DB is always identical backed up one (like mirror copy). Thanks a lot, Gail for your time and detailed explanation. Yuri

Viewing 13 posts - 1 through 12 (of 12 total)

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