Solved

Setting data/log file locations in SQL Server 2005 not working when upsizing from Access.

Posted on 2008-09-29
6
454 Views
Last Modified: 2012-08-13
I am trying to upsize a large Access database to SQL Server 2005.  I have limited space in the normal location where SQL Server creates its data and log files (namely, folder C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data).  In Management Studio, I opened the properties for the server, selected Database Settings, and changed the "Database default locations" for Data and Log to a larger volume.  (Happens to be Z:\SQL-Data.)

When I use Integration Services to import data into a new database, it uses the location I have specified (the Z: drive).  However, when I run the Access Upsizing Wizard, the data and log files are still being created on the C: drive.  I'm guessing there is a registry key that could be changed, but I would prefer to know where this file path can be designated within the Managment Studio or some other utility.  Thanks!
0
Comment
Question by:AutomateMyOffice
[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
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22600278
I am not sure the upsizing wizard takes any setting in that regards...

and in case there is really no such thing, you will have to move the database after the upsize, with either:
* BACKUP + RESTORE (during restore, you can specify new file locations)
* detach + move + attach (check out the sp_detach_db and sp_attach_db functions)
0
 

Author Comment

by:AutomateMyOffice
ID: 22600598
I would not expect to designate this within the upsizing wizard.  I would, however, expect that there is some setting within SQL Server for this purpose.  I thought I had found it within the Database Setting pages, but apparently not.  

I am aware that backup and restore can be used to move the db files after creation; the problem is that I do not have enough space on the C: volume to create the first instance.
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 500 total points
ID: 22601549
HI AutomateMyOffice,

You can Detach and Attach to move them also which is probably quicker. If you're using upsizing wizard then you probably should look at SQL Server Migration Assistan for Access freely downloadable from M$.


Jim
0
 

Author Comment

by:AutomateMyOffice
ID: 22783040
Sorry for the neglect.  The question was whether there was a way to change the location used by the Access Upsizing Wizard, since I lacked the space on the drive that was being used.  Backup and detach were not helpful since there was not enough space to create the files in first place.  I award the points to Jim since the SQL Server Migration Assistant does allow the location to be designated and is FAR superior to the Access Upsizing Wizard in accomplishing the desired task.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22783063
Thanks AutomateMyOffice. I don't know why they don't do away with the upsizing wizard.

Jim
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
spx for moving values to new table 5 75
Not selecting duplicate data 6 64
tempdb log keep growing 7 44
How can I use this function? 3 32
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

763 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