We help IT Professionals succeed at work.

Can I restore a .bak and either not restore the LDF or split the MDF?

joshualeone asked
Medium Priority
Last Modified: 2012-05-07
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?
Thank you.
Watch Question

Database Architect - Dba - Data Scientist
Unlock this solution and get a sample of our free trial.
(No credit card required)


Thank you for your help, I think that ultimately if I move the bulk of the database or "empty" the initial mdf then I won't see the performance hit of going through the USB bus to get to the data.  So if I can figure out how to create additional files and move the data into those files I will get the file access speeds I need in order to side step the performance I've been experiencing on the other system.


So I did the restore putting the MDF on a USB drive.  Once the restore was done I went into SQL Server Management Studio and created additional datafiles in the same file group.  Then I went and ran
dbcc shrinkfile (data, EMPTYFILE)
For a little while I can see the new NDF files grow then it seems to hang. If I go into activity and take a look I see the status listed as suspended and the wait time growing and the wait type listed as pageiolatch_sh.
The reading I've done seems to indicate that this is a problem with disk IO.  I have seen articles that indicate that when doing a standard shrink file it's a good idea to do it in stages.
So I checked SP_who2 and the cputime and diskio are standing still.
Do I just need to be more patient? Should I be doing this different?
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

<<Do I just need to be more patient? Should I be doing this different?>>
This kind of thing may happen when using a USB drive on above 100Gb database files.  As to what to do next I believe I have already answered that question...

Good luck...
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.