Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Ok my question is simple.

Lets say that when i click database and hover over shrink it says it can be reduced 1 GB space.

Now when i do a backup of this database, does this space also getting added to the backup file or backup process is somewhat special and it already shrinks it ?

SQL server 2012

share|improve this question
add comment

2 Answers

The backup process does not back up empty pages, only pages with data, so typically, the backup file will be a lot smaller.

One important thing to note: when using compression, the initial size needed on the destination drive will be the uncompressed size; the backup will only be fully compressed at the end of the process.

share|improve this answer
3  
+1 It's also worth noting that the documented trace flag 3042 will bypass bloated pre-allocation for compressed backups in case that space isn't originally available. –  Thomas Stringer Nov 12 '13 at 13:22
    
@ThomasStringer I'll add the caveat (not for you, but for readers in general) that TF3042 can affect the performance of the backup. Bob Ward explains it pretty nicely here. –  Aaron Bertrand Nov 12 '13 at 14:16
    
Oh absolutely, completely agreed. Should only be used if the free space isn't there. –  Thomas Stringer Nov 12 '13 at 14:20
    
@AaronBertrand The intital size is actualy 1/3 and will grow as needed and if it doeses cause a slight performance penalty support.microsoft.com/kb/2001026 . As Tomas remarks trace flag 3042 starts from 0 and grows as needed. –  buckley Dec 6 '13 at 10:24
add comment

If it is empty space within the file then it makes a small difference to the backup size, I would assume that it has to write into the backup file somewhere that there is all this empty space available within the file. It doesn't back up all the empty space though, so if you have a data file with 1gb of empty space inside it, the backup file won't be 1Gb smaller if you shrink the file before backing up.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.