Solved

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

Posted on 2009-07-09
4
436 Views
Last Modified: 2012-05-07
Hello,
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.
0
Comment
Question by:joshualeone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
ID: 24819573


<<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>>
SQL Server can't do a RESTORE/SPLIT operation at once.  The data must be restored as it originated before being split.  Besides, assuming you are trying to reconstitute the database in tuning perspective, I would not advise having a local restored database different, file wise from the original database.  

<<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. >>
That may work but will take a *while* to achieve the desired objective.  

The idea being:
> Restore on a large cheap disk
> Add files on the available drives in same filegroup
> Empty the USB hosted file using DBCC..EMTPYFILE option

But be also aware that you may have some surprises into deleting the old file when the file is emptied.


<<I have also read hints about performance tweaking having to do with the temp db and multiple cores.  >>
Performance is *much* more than about splitting tempdb files or reorganizing files physically.  (The splitting of tempdb files on the number of cores was simply a bug fix to a problem recognized by MS).

Since you *both* have a hardware and a training handicap in performance tuning, my best advice to you is to consider getting some *serious* training and favor onsite analysis before you make any recommendations.   The fact of getting closer to production conditions will allow you to make decent analysis and it will avoid you to be put in embarassing situations.  At the bottom of the following page you can find links to books on performance tuning

http://vyaskn.tripod.com/com_isolation_level.htm

HTH
0
 

Author Comment

by:joshualeone
ID: 24819778
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.
0
 

Author Comment

by:joshualeone
ID: 24850405
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?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24850852
<<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...
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
section a string 5 49
Retrieve a Registry value using SCCM 2012. 2 38
SQL SERVER 2008 R2 Problem copying database 10 62
Does INTERSECT return opposite from FULL OUTER JOIN? 4 32
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question