October 11, 2012 at 5:09 am
when backup is taken can anyone tell me as to what exactly happens in the background
October 11, 2012 at 5:21 am
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
October 11, 2012 at 6:42 am
newbie wanting to understand sql
October 11, 2012 at 8:29 am
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
October 14, 2012 at 4:05 pm
What means "used pages"? Can you please clarify. Thanks
October 15, 2012 at 12:05 am
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;-)
October 15, 2012 at 9:56 am
Bhuvnesh (10/15/2012)
Yuri55 (10/14/2012)
What means "used pages"? Can you please clarify. Thanksused 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
October 15, 2012 at 10:38 am
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
October 15, 2012 at 2:28 pm
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
October 15, 2012 at 3:00 pm
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
October 15, 2012 at 3:38 pm
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
October 16, 2012 at 2:34 am
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
October 16, 2012 at 8:34 am
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