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]

Just as a test what happens if you try to move it to the root of E:. If that fails try the root of F:\.

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

 

[Ronel17]

Same thing.

 

[ptheriault]

So you can't move the tempdb at all?
How have you logged into SQL?

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

 

[Ronel17]

Paul,
I use this:

ALTER DATABASE tempdb MODIFY FILE ( NAME = templog , FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\templog.ldf' )

It's working.

Ron

 

[ptheriault]

So you only changed the case of 'MODIFY FILE'?

Well, I'll have to remember that one.

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

 

[Ronel17]

All right thank you very much for the help.
Ron

 

[Ronel17]

Hey Paul,
Another question.
I need to restore my data from an SQL2000 to a new box 2005.
Do I need to restore my system data or just my userdata?

Thanks

Ron

 

[ptheriault]

just your user data. If you want your scheduled jobs restore the msdb database as well.


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

 

[Ronel17]

Thanks man.
Ron

 

[ptheriault]

You might want to migtrate your user accounts too. If you don't you will have to run sp_change_users_login to sync up the user SIDS in the database with the server.

There is an faq on how to do that in the faq section of this forum.

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

 

[Ronel17]

Ok Thanks. I'm restoring the data now, I'll let you know. Thanks again for your help.

Ron