Solved

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

Posted on 2009-07-09
4
427 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
  • 2
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
Comment Utility


<<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
Comment Utility
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
Comment Utility
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
Comment Utility
<<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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now