Moving database to new path in the same server SQL 2005

[Ronel17]

Hi All,
I'm new to SQL. In a fresh insatllation I'm trying to move the database to an another drive and the logs to a separate drive. Help please.
Ron

 

[ptheriault]

Are these system databases or user databases?

To move a user database you have to detach it. Right click on the db and select tasks-> detach.
Then copy the .mdf and .ldf files to the location you want.
Go back to SSMS right click on Databases and select attach.
Enter the path to your .mdf and .ldf files.

If you need to move your system databases post back and I'll give you instructions.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Ronel17]

Since this is a fresh install. I uninstall SQL2005.
My new question is how do I install SQL 2005 and choose where to put my Data (E) log (F) Tempdb (F).
Hope you can help.
Thanks

on

 

[ptheriault]

You can't do that on the install. You can select the advanced option on the third or fourth screen to select your install path. But after the install you would have to move your databases.



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Ronel17]

Thanks

Ron

 

[ptheriault]

No Problem
If you have any questions on how to move databases after you install see this thread.

thread962-1345781

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Ronel17]

use master
go
Alter database tempdb modify file (name = tempdev, filename = '
F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = '
F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\templog.ldf')
Go



Will that be ok, to move my temp db from a drive G to a drive F?

Thanks

 

[ptheriault]

Yes,
You will need to restart the services for the move to take effect.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Ronel17]

This what I get:


Msg 5121, Level 16, State 1, Line 1
The path specified by "
E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf" is not in a valid directory.
Msg 5121, Level 16, State 1, Line 1
The path specified by "
E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\templog.ldf" is not in a valid directory.

 

[ptheriault]

The folders need to exists before you make the change.

Create the folders
E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\

Then run it again.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Ronel17]

I did.

 

[Ronel17]

Paul,
Any idea?

 

[ptheriault]

Are there other db's on that drive?
Can SQL see the E: drive?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

Is E & F a MAPPED drive? If so, you can't use them.

BTW- For user databases, once your install is done, right click on the instance in Enterprise Manager, select properties. Go to the Database Settings tab and you can set where you want the data and log files to go when you create a new database.

-SQLBill

Posting advice: FAQ481-4875

 

[Ronel17]

Hi Paul,
No there is no other db on that drive. Yes I can see the E: drive from SQL.

Ron

 

[Ronel17]

Hi Bill,
No there not map drive. I'm talking about Syatem database, the tempdb.
Thanks

Ron

 

[ptheriault]

Ronel,
Are you sure you don't have a type-o somewhere in the path or in you TSQL?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Ronel17]

Paul,
That was the first thing I checked.
Here is the path on the server where I want it:
E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Now I have it in :
F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

And I use:
use master
go
Alter database tempdb modify file (name = tempdev, filename = '
E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = '
E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\templog.ldf')
Go


Thanks

Ron

 

[ptheriault]

Ron,
That looks correct. So I have a few more questions.
1 Is this server a cluster?
2 Is the E:\ drive a dynamic disk?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Ronel17]

Paul,
1)No it's not a cluster.
2)No

Thanks

Ron