1

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.

0

3 Answers 3

0

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.

2
  • that's what i'm trying to say... it is not restoring to the size it was backed up from. the backup file is made from the smaller 700gb copy of the database... but that bak file when restored, becomes 1.4tb.
    – sam yi
    Sep 11, 2020 at 22:17
  • So you have a data file which is, say 100 GB when you do the backup. And when you restore this in the target environment, it larger than 100 GB - immediately after the restore. That shouldn't happen. Investigate which backups in you in the backup file using RESTORE HEADERONLY, and investigate the backup in the backup file which you are about to restore using EASTORE FILELISTONLY (using the FILE option if it isn't the first backup in the backup file). My guess is that you aren't restoring the right backup. Sep 12, 2020 at 12:23
0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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