?
Solved

SQL 2000 Move Log Files to Different Drive

Posted on 2011-02-23
10
Medium Priority
?
536 Views
Last Modified: 2012-05-11
We currently have a SQL 2000 volume that has it's database files on one drive and the log files on another.

I wanted to move the log files to another drive that what is on now. When I try to review the properties and change locations it does not let me modify. When I add a new location (as a secondary) and detach and re-attach it does not link to the newly reference drive.
0
Comment
Question by:RTM2007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 2
10 Comments
 
LVL 6

Accepted Solution

by:
anushahanna earned 2000 total points
ID: 34964197
ALTER DATABASE DBName SET OFFLINE with ROLLBACK IMMEDIATE


ALTER DATABASE DBName
   MODIFY FILE (NAME = DBName_Data, -- This is the logical name
      FILENAME = 'D:\Data\DBName_Data.mdf') --This is the new location

ALTER DATABASE DBName SET ONLINE
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 34964208
after you do the MODIFY FILE statement, physically copy/paste the file from the old location to the new one. otherwise the SET ONLINE will give error.
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 34964223
to find the location file name, run
sp_helpfile
and the name column is the logical name.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 2

Author Comment

by:RTM2007
ID: 34964224
I am not too familar with SQL so please elaborate. Is that what I would need to put into the SQL Query Analyzer?

The database name is TRA_100 and it is going from the C:\Program Files... to D:\Logs
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 34964228
sorry i meant logical file name
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 34964258
yes you will need to run in query analyzer. But remember when you do the below, no user will be able to use the application, so do it while there is a maintenance window.

ALTER DATABASE TRA_100 SET OFFLINE with ROLLBACK IMMEDIATE


ALTER DATABASE TRA_100
   MODIFY FILE (NAME = TRA_100_Log, -- This is the logical name
      FILENAME = 'D:\Logs\TRA_100_Log.ldf') --This is the new location

--Now, copy the file from C:\Program FIles to D:\Logs, and bring the database online again

ALTER DATABASE TRA_100 SET ONLINE
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 34964284
in the example i have above
TRA_100_Log is the logical name and
TRA_100_Log.ldf is the physical name.

if you run in a seperate window- the below

USE TRA_100
EXEC sp_helpfile

you can confirm the real logical and physical files you have and that you should be using.
0
 
LVL 2

Author Comment

by:RTM2007
ID: 34971145
Ok that worke dfor the TRA logs, but with another database/log file I am having a problem for some reason:

Here is the syntax:

ALTER DATABASE EVVSMailboxVaultStore_2 SET OFFLINE with ROLLBACK IMMEDIATE


ALTER DATABASE EVVSMailboxVaultStore_2
   MODIFY FILE (NAME = EVVSMailboxVaultStore_2LOG,
      FILENAME = 'D:\Data\EVVSMailboxVaultStore_2LOG.ldf')
---------------------------

Query Analyzer gives me an alert that the database is offline.

---------------------------

USE EVVSMailboxVaultStore_2
EXEC sp_helpfile

Outputs:

That the EVVSMailboxVaultStore_2LOG70 is still in the C:\Program Files directory. Also just to confirm I have to be in the master DB to run this query in the analyzer correct?
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 34976594
OK- did you copy the file from C:\Program files to D:\Data... once you do that, then bring it online again.

again the order is important.
run the exec sp_helpfile first itself to make sure where it is

bring it offline
ALTER DB... MODIFY FILE.... command
copy/paste
out it online
done

run the exec sp_helpfile again to make sure where it is (should be new place)

please let me know if there are any issues you find.
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 35000282
RTM2007, did it work for you? any issues? let me know.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question