Link to home
Start Free TrialLog in
Avatar of mike99c
mike99c

asked on

Changing log and data location in SQL Server 2008 is not having any affect

I have a SQL Server 2008 R2 database and I am trying to change the location of the data and log files from the C drive to the D drive.

I have followed the instructions here:
http://technet.microsoft.com/en-us/library/dd206993.aspx

When I create a new database, it correctly created the data and log files in the new location on the D drive. However I was hoping to move the location of an existing database by doing the following:

1. Backup the database
2. Delete the database
3. Restore the database

The problem is when I do the restore it still saves the data and log files in the old D drive location. I am aware there are commands to alter the database etc but I was hoping by doing the restore it would save everything in the new location.
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You need to restore with 'Move' option.

Another way of doing this is, take the database offline. Move the files to the proper places, drop database, and attach the database by browsing these files.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mike99c
mike99c

ASKER

Found the answer. During the restore, click on "Options" on the left. Then simply change the file location under the "Restore As" column.
Avatar of mike99c

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for mike99c's comment #a39483649

for the following reason:

This is the exact solution.
Avatar of mike99c

ASKER

Apologies everyone as I just found the solution myself before checking Experts Exchange. Happy to split the points however as I don't want you to think I did anythig underhand.