I understand all the reason why it's not advisable to shrink
I only preface this to avoid falling into that conversation.. so, I have 1.4tb database.. about 700gb is free. This is due to the fact that people have been really irresponsibly making duplicate copies of the data and never cleaning up. In other words, this is space that should be reclaimed.
There are cases when I need to restore 3-4 copies of this database on the same server due to many concurrent development effort that's taking place. In this case, that 700gb waste is multiplied 3x-4x. So, I've been restoring the database to a DBA server then shrinking then backing up the file to be distributed to the lower environment. However, once that 700gb database is restored, it balloons backup to 1.4tb. I thought maybe the "initial size" of the files is doing this but when the backup is taken, the initial file size is small.
As a workaround, we are dropping the files and consolidating. There are 7 data files and we're dropping 2 of them. I would rather avoid this and try to figure out why the data files are ballooning.
Best Answer
The restored database will have the same file size as it had when the backup was produced.
I sometimes call the files "containers". Same number of containers, and same size. They don't have to be at the same location, though (the MOVE option of the RESTORE command).
I don't know what "initial" you refer to above, but whatever it is, it is probably misleading you - and it doesn't change the facts I mention above. :-)
So if you backup a database which has a 100 GB file, that file will be 100 GB when you restore form that backup. If you see something else, then you are probably in a parallel universe. ;-) OR you need to be extremely specific so we follow you to 100% of what is happening. One thing that can be useful here is RESTORE HEADERONLY and RESTORE FILELISTONLY. The later will tell you what size the files will be when you restore.