Solved

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

Posted on 2009-07-09
4
429 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
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

863 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

26 Experts available now in Live!

Get 1:1 Help Now