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

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!

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

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)

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.

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$.

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.

I am showing a way to read/import the excel data in table using SQL server 2005...
Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name".
Now to import this Excel data into the table, we will use…

I've encountered valid database schemas that do not have a primary key. For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis. However, occasionally due to user error or a scheduled task…

Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg).
If you're looking for how to monitor bandwidth using netflow or packet s…