if database is not shrinked does it affect backup size?

Posted on

Question :

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

Answer :

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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *