1

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.

Andy Smith
  • 1,798
  • 13
  • 15
HarleyP
  • 13
  • 2
  • 1
    You don't already have monthly backups for all of them? – ceejayoz Nov 30 '11 at 21:53
  • 1
    We backup the entire database nightly sure, but that is a backup of the entire database. But this customer wants a backup of their data to download from us to store on their own location. We can't give them our nightly backup and just say "please don't look at any of the other customers data"... – HarleyP Nov 30 '11 at 23:41
  • 1
    I kinda want to suggest that you develop a program similar to how mysqldump works. Simply hack up a script that will generate the a text file with the DDL and Insert statements to completely recreate a new database. – Zoredache Dec 01 '11 at 00:37
  • 1
    Why not just use DTS or SSIS to export only the data needed to a new database? – joeqwerty Dec 01 '11 at 00:48

1 Answers1

0

I suspect that the restore process is creating a 95 GB database. from shrinkdatabase:

The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database is originally created, or the last size explicitly set by using a file size changing operation such as DBCC SHIRNKFILE or ALTER DATABASE. For example, if a database is originally created with a size of 10 MB in size and grows to 100 MB, the smallest the database can be reduced to is 10 MB, even if all the data in the database has been deleted.

I would change the way you are doing this by writing a script that creates a new database and does a select into for the customer data

Jim B
  • 23,938
  • 4
  • 35
  • 58
  • Hrm this makes sense, though if that's the case wouldn't the shrinks do nothing at all? As I mentioned I can get it down to 15-20gb in size after many many hours of shrinking, so it doesn't seem like it's restricting it to the 95gb size really. – HarleyP Nov 30 '11 at 23:43