We have a multi customer database, that's about 95gb in size. One of the customers is requesting a monthly backup of their data. We have a script that strips out all records from all tables that are not related to the specified ID.
So we restore backup of DB to a new DB, run our script to strip it down, this leaves us with a DB containing 1 customer instead of 100ish. But the DB is still 95gb in size even though it should now be 90%+ empty.
I've tried many different combinations of taking it offline, then back online, backing it up after the strip down, various different combinations of DBCC SHRINKDATABASE and DBCC SHRINKFILE, etc.
The best I've done so far is about 9 hours worth of shrinking to get the file down to about 15gb. The most each shrink ever takes off is 10-20%, even when I specify to leave 0 free space behind.
Several of the tables contain columns with the image datatype, and I think that has something to do with it, but I still can't seem to find a reliable solution to this.
Ultimately the goal is to have a Plan/Job that will run once a month to restore a backup to a new DB, strip it down, shrink it down, then back that up to a folder and delete the new DB. But we really would like to make this happen in less than 9-12 hours if possible.
Any help would be appreciated.