Database Shrink, Backup, then Restore

Question

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.

Answer 1

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.

Answer 2

Sometimes databases can be huge due to high fragmentation.Check the free space in each Datafile, if you think you have more free space..you can go for shrinking the database (which is not at all a best practice). But if you do so..make sure you are running the rebuild index and upate stats jobs inorder to save u from any performance related issues

Answer 3

However, once that 700gb database is restored, it balloons backup to 1.4tb.

After you restore the 700GB database into the target environment, limit the file growth and see what breaks.

Also evaluate columnstore indexes and an upgrade off of SQL 2014. In SQL 2016 and later columnstores are compatible with non-clustered unique and non-unique indexes and non-clustered columnstore indexes are updatable.