Moving sqllogs folder from one drive to another

[web4fun]

SCENARIO/SETUP:

SQL Server 2005 with SP2

Server has:
C:drive with 50GB on local storage
D:drive with 500GB on NetApp SAN (SQL Server, dbs, installed on D:drive)
E:drive with 500GB on NetApp SAN (SQL Server logfiles and SQL Server backups store on E:drive)

PROBLEM/QUESTION:

Need confirmation/advice on moving my 'sqllogs' folder which contains the transaction logs, etc. to a different drive; i.e. from Drive E: to Drive D:.

STEPS TRIED:

1) I had tried changing the default logfile location in Management Studio for the server to point to D:\sqllogs from E:\sqllogs

2) Stopped SQL Server

3) Copied everything over from E:drive to D:drive. This included the sqllogs and backups folders

4) Started SQL Server

5) Modified all Maintenance Plans to backup/cleanup to D:drive instead of E:drive

PROBLEM:

When attempting to view the tables for ALL of the User Databases, you are unable to view any of them.....cannot view properties for the databases...nothing...no plus sign next to the databases to expand anything!

Error logs show that SQL Server is still looking for log files on the E:drive? Why? Have I missed a step???

So, for now to correct the problem I recreated the E:drive and moved everything back over to the E:drive and everything is fine....but this should be possible to do correct?

Thanks.

 

[ptheriault]

To move the log files for system databases you use the ALTER database command.
Here is an example using the tempdb

use master
go
Alter database tempdb modify file (name = templog, filename = 'E:\programs\mssql\data\templog.ldf')
Go

For the master database you change the -l parameter in the startup paramaters of SQL Server. You can find those parameters in the SQL Server Configuration Manager.

Then restart the services.

For user databases you can detach them, move the .ldf file then attach them

Transformation Services
Microsoft SQL Server: Integration Services (SSIS)
Microsoft SQL Server: Setup and Administration
Microsoft: Access Queries and JET SQL
Microsoft: Access Other topics
Oracle: Oracle release - 9i
Oracle: Oracle release 10g
Sybase: Adaptive (SQL) Server
Macromedia: ColdFusion
Microsoft: ASP.NET
C# (C sharp): Microsoft
Javascript
Visual Basic(Microsoft) -VB.NET
Visual Basic(Microsoft): Version 5 & 6
Server Rack
VERITAS: Backup Exec back-up
- General NAS/SAN discussion
Partners
"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)
Feedback
"...An excellent site which has quite possibly prevented me from having a mental/nervous breakdown..."
More...
Geography
Where in the world do Tek-Tips members come from?
Click Here To Find Out!
Partners
ASP Alliance
Code Project
Developer Fusion
Developers Dex
devGuru
Planet Source Code
Programmers Heaven
Tek-Tips Forums


Advanced Search

Google
Web Tek-Tips Forums
Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Setup and Administration Forum
Moving sqllogs folder from one drive to another
thread962-1352189
Forum Search FAQs Links Jobs Whitepapers
Read
New Posts Reply To
This Thread
E-mail It
Print It Next
Thread
web4fun (MIS)
2 Apr 07 8:59
SCENARIO/SETUP:

SQL Server 2005 with SP2

Server has:
C:drive with 50GB on local storage
D:drive with 500GB on NetApp SAN (SQL Server, dbs, installed on D:drive)
E:drive with 500GB on NetApp SAN (SQL Server logfiles and SQL Server backups store on E:drive)

PROBLEM/QUESTION:

Need confirmation/advice on moving my 'sqllogs' folder which contains the transaction logs, etc. to a different drive; i.e. from Drive E: to Drive D:.

STEPS TRIED:

1) I had tried changing the default logfile location in Management Studio for the server to point to D:\sqllogs from E:\sqllogs

This is for new databases only. It will not change existing databases.

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

 

[web4fun]

Thanks Paul....so your last line "This is for new databases only. It will not change existing databases." Does that mean that for my existing User Databases that I can not move the .ldf file location from E: to D:?

 

[ptheriault]

You can move existing databases by using the detach and attach method.
Or
Restore database with Move option.

Sorry about that Quote I only meant to highlight.
>>STEPS TRIED:
1) I had tried changing the default logfile location in Management Studio for the server to point to D:\sqllogs from E:\sqllogs

That setting is so when you create a new database it will default the log to E:\sqllogs

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

 

[web4fun]

Thanks Paul...I've got it...much appreciated sir.

 

[web4fun]

Paul...forgot one thing....regarding security and the current logins associated to the user dbs, I will need to run the SQL orphan scripts won't I as won't the logins on the other server be orphaned at that point....unless of course I move the 'master' db also correct?

 

[ptheriault]

Don't move the master.

Take a look at this faq.
faq962-6608



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