InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 08:40:49 GMT20http://www.sqlservercentral.com/Forums/Topic1371360-357-1.aspxTo resume- restored DB is always identical backed up one (like mirror copy). Thanks a lot, Gail for your time and detailed explanation. YuriTue, 16 Oct 2012 08:34:27 GMTYuri55http://www.sqlservercentral.com/Forums/Topic1371360-357-1.aspxSame 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.Tue, 16 Oct 2012 02:34:08 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1371360-357-1.aspxMake 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? ThanksMon, 15 Oct 2012 15:38:00 GMTYuri55http://www.sqlservercentral.com/Forums/Topic1371360-357-1.aspxThe 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)Mon, 15 Oct 2012 15:00:23 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1371360-357-1.aspxSorry 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? ThanksMon, 15 Oct 2012 14:28:08 GMTYuri55http://www.sqlservercentral.com/Forums/Topic1371360-357-1.aspxBecause restoring a backup always recreates the database exactly as it was at the time the backup was taken.Mon, 15 Oct 2012 10:38:56 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1371360-357-1.aspx[quote][b]Bhuvnesh (10/15/2012)[/b][hr][quote][b]Yuri55 (10/14/2012)[/b][hr]What means "used pages"? Can you please clarify. Thanks[/quote] used means the pages which contain data .refer this link [url]http://msdn.microsoft.com/en-us/library/ms175477(v=sql.90).aspx[/url][/quote]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? ThanksMon, 15 Oct 2012 09:56:58 GMTYuri55http://www.sqlservercentral.com/Forums/Topic1371360-357-1.aspx[quote][b]Yuri55 (10/14/2012)[/b][hr]What means "used pages"? Can you please clarify. Thanks[/quote] used means the pages which contain data .refer this link [url]http://msdn.microsoft.com/en-us/library/ms175477(v=sql.90).aspx[/url]Mon, 15 Oct 2012 00:05:30 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1371360-357-1.aspxWhat means "used pages"? Can you please clarify. ThanksSun, 14 Oct 2012 16:05:11 GMTYuri55http://www.sqlservercentral.com/Forums/Topic1371360-357-1.aspxWell, 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.Thu, 11 Oct 2012 08:29:29 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1371360-357-1.aspxnewbie wanting to understand sqlThu, 11 Oct 2012 06:42:23 GMTatulyan.arieshttp://www.sqlservercentral.com/Forums/Topic1371360-357-1.aspxWhy do you want to know? What problem are you investigating?Thu, 11 Oct 2012 05:21:53 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1371360-357-1.aspxwhen backup is taken can anyone tell me as to what exactly happens in the backgroundThu, 11 Oct 2012 05:09:39 GMTatulyan.aries