November 17, 2008 at 9:23 pm
I have Data File 2500 MB with 500 MB Available space and 2000 MB Reserved Space.
I also have Log File 500 MB with 50% usage so that 250 MB is still free.
If I do a full database backup then how big is the file size of the backup result?
For data file, do I get 2000 MB backup file or 2500 MB backup file? I mean whether the 500 MB available space also included in the backup file or not?
What about the log file, do I get 500 MB log backup file or 250 MB log backup file?
Should I shrink the data file and log file before the database backup?
Thanks.
Ivan Budiono
November 18, 2008 at 10:04 am
Is this theoretical or actual ? If actual, then you should already have backups and you can look at the file size. The available space will not be in the backup.
Don't shrink either file unless absolutely necessary.
November 25, 2008 at 10:19 am
If you don't have backup of the db and want to find out what would be its size if you backup, I believe you can run the following query to find out:
SELECT SUM(CONVERT(NUMERIC(10,2),(fileproperty(Name, 'SpaceUsed')/128.0),2))
FROM SYSFILES
WHERE GroupID > 0
and changing > 0 to = 0 should give you the transaction log file size, once backed up, I believe.
December 4, 2008 at 1:32 pm
The backup file contains the system objects, and then all the data in your database, but it contains a portion of the log as well. Any changes that were done between when the backup started and when it finished (from the log) are included. This is what is the roll-forward/roll back when it is restored.
For log files, you get the used log space (plus some overhead).
Tracking backup file sizes is a good way to keep an eye on how much data you actually have.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply