0

I would like to understand, what are the flaws following scenario:

Supposing I have a database "A" which is used to acquire data from somewhere (let's say CSV file), and then this data continues to some other database "B". All of these (CSV, A, B) are hosted on the same machine, with one drive to be utilized. My drive, theoretically, only has 10GB of space.

Let's assume the CSV has 5GB and the same data uses 5GB in the database "A" (I am not using compression). This means when CSV is on the drive and "A" is loaded, whole 10GB drive is full. When I want to transfer the data from "A" to "B", I will delete CSV, so that 5GB is empty and then I will fill "B", so that again, whole drive is full.

Supposing I have a new CSV file and need to keep data in "B", I would like to reduce size of "A", but keep the whole "A's" structure, as well as rights, etc.. The obvious possibility is to use truncate and then shrink - but then again, I only need "A's" structure and empty tables. Shrink takes long to process, introduces fragmentation and indexes grow over time because of it.

Would it be possible to have an empty (no data, but already with tables, procedures, rights, etc..) backup version of "A" that I would use in a way that I would drop whole "A" and restore it from backup rather than doing truncate of all tables in "A" and shrinking it?

What are the flaws behind this thinking? Can the backup hold all the rights and connections that were associated with the original "A" database?

Filip Kraus
  • 2,714
  • 2
  • 14
  • 16
  • Yes, you could restore template database from a backup. But why create this problem mucking about with 10GB of space? I'm holding my phone which has 128GB, I can buy a terabyte drive for next to nothing. Just buy a bigger drive and spend your time on actual problems! – Stu Oct 14 '21 at 11:40
  • This is theoretical question so that I can do easy calculation examples, my database in reality has over 8TB of space and the truncate + shrink would save approximately 2TB after every iteration. My customer does not want to allow for more space at the moment. Also because of the real size of the problem, I think drop and restore will save much more time. – Filip Kraus Oct 14 '21 at 11:45
  • One more think to consider - 8TB might seem like not much, but in enterprise application, this actually is a lot - not just that it is SSD's, but the implementation of another 2TB does not really mean 2TB overall, as you have multiple environments this is related to as well as many other restrictions the actual enterprise has. – Filip Kraus Oct 14 '21 at 11:48
  • Restoring a backup of the default empty database is fine if that's what you need, the backup contains all properties, meta data, permissions etc so it's a viable "reset" option. – Stu Oct 14 '21 at 11:48
  • Will the restore somehow affect other solutions that are connected to this database? I do not mean during the time the DB will be dropped and restored, but rather after it is restored - does the restored DB keep the same connection information, access rights, etc? – Filip Kraus Oct 14 '21 at 11:50
  • Why don't you try testing it and see. – Stu Oct 14 '21 at 11:52

0 Answers0