Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

backup background process Expand / Collapse
Author
Message
Posted Thursday, October 11, 2012 5:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:12 AM
Points: 41, Visits: 151
when backup is taken can anyone tell me as to what exactly happens in the background
Post #1371360
Posted Thursday, October 11, 2012 5:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
Why do you want to know? What problem are you investigating?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1371371
Posted Thursday, October 11, 2012 6:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:12 AM
Points: 41, Visits: 151
newbie wanting to understand sql
Post #1371425
Posted Thursday, October 11, 2012 8:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
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 2008, MVP
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

Post #1371539
Posted Sunday, October 14, 2012 4:05 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:23 PM
Points: 424, Visits: 1,549
What means "used pages"? Can you please clarify. Thanks
Post #1372543
Posted Monday, October 15, 2012 12:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1372564
Posted Monday, October 15, 2012 9:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:23 PM
Points: 424, Visits: 1,549
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
Post #1372794
Posted Monday, October 15, 2012 10:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
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 2008, MVP
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

Post #1372823
Posted Monday, October 15, 2012 2:28 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:23 PM
Points: 424, Visits: 1,549
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
Post #1372939
Posted Monday, October 15, 2012 3:00 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
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 2008, MVP
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

Post #1372952
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse