January 18, 2012 at 12:53 am
When we take the full backup of database in sql 2008 with/without selecting the compressed backup option, does it contain the free space of database as well OR just the data?
----------
Ashish
January 18, 2012 at 1:02 am
Just data. No free space of DB.
January 18, 2012 at 1:06 am
thanks. If that is the case then when we restored the database why our mdf and ldf comes with same size including free space.
----------
Ashish
January 18, 2012 at 1:10 am
Just want to add, when you restore, you get the same free space back on the restored Db.
Regards,
Raj
January 18, 2012 at 1:14 am
When you restore you want the exactly the same set up including the Filegrowth, free space available etc...Especially useful if you have allocated your DB a specific amount of space estimating a future growth. However, when you backup you just need the data and ideally would want the backup to be as small as possible. So free space is not backed up and upon restoration it is back.
Regards,
Raj
January 18, 2012 at 1:17 am
ok, so then how sql come to know while restoring the database that it need to add the specific amount of free space in database files and as exact of free space as it was there in database before taking the backup?.
----------
Ashish
January 18, 2012 at 1:27 am
Though I am not sure ,free space and fragmentation info should be present in Backup header.
Better expert will confirm 🙂
Regards,
Raj
January 18, 2012 at 1:44 am
crazy4sql (1/18/2012)
ok, so then how sql come to know while restoring the database that it need to add the specific amount of free space in database files and as exact of free space as it was there in database before taking the backup?.
This may not be the best answer to this question, still....
Data files are internally devided into extents and pages. File header has information about all the extents and pages.
Empty extents are not backed up. But restore creates all the pages and extents as it was.
Restore does NOT simply add free at the end to match the file size.
Just think of the differential backup/restore. Changed pages/extents go to their respective location in the file.
January 18, 2012 at 3:58 am
crazy4sql (1/18/2012)
ok, so then how sql come to know while restoring the database that it need to add the specific amount of free space in database files and as exact of free space as it was there in database before taking the backup?.
Because that's the way it's designed. A restore recreates the database exactly as it was at the time of backup. Exactly. That includes free space at the exact same locations in the file, the exact same file size, etc.
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
January 18, 2012 at 4:51 am
Thanks Gail and all.
----------
Ashish
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply