Can I restore a .bak and either not restore the LDF or split the MDF?
Posted on 2009-07-09
I work for a support organization and occasionally as a troubleshooting step we have customers send us backups of their database in order to restore in house and work on the problem locally.
The most recent backup I received has presented a bit of a problem. The .bak is 146gigs in size.
Some time ago we did a restore of the same customers backup and at the moment there is one mdf that is 167.6 gigs an NDF that is 9.9 gigs and an LDF that has been shrunk that is somewhere on the order of 200 megs. Unfortunately this data set is proving too big for the server we restored it on so we have started to restore it on a faster server.
The new server is on loan so I don't have full control over it the way it is currently laid out is a set of 5 drives that are 136 gigs each.
The problem being that even if I clean one of those drives off I'm still stuck with an MDF that is 167 or so gigs and won't fit.
I've been doing some reading but I haven't found anything that solves my problem yet. I've read that you can create new file groups and move tables into them, but I haven't been able to identify find more detail on how to do that.
My thoughts at the moment are to try one of the following
1. Find a way to split the mdf on restore so that it restores onto multiple drives, if possible shrink the LDF during restore so it doesn't take up that much space
2. Restore the MDF onto an external USB drive as a temporary measure and then use some combination of additional files or new file groups to move the tables into another MDF or NDF across the different SCSI drives. This hopefully will also improve performance which is the ultimate goal of this restore though at the moment I am simply faced with the technical problem of actually getting the database restored.
The .bak is stored on a USB drive as the source of this restore.
I have also read hints about performance tweaking having to do with the temp db and multiple cores. This machine has 8 processors so any performance tweaks that can make use of that would be most helpful.
Is there a solution I've missed?
Can you offer advice on how to accomplish 1 or 2 or some hybrid there of?