RTM2007
asked on
SQL 2000 Move Log Files to Different Drive
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
to find the location file name, run
sp_helpfile
and the name column is the logical name.
sp_helpfile
and the name column is the logical name.
ASKER
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
The database name is TRA_100 and it is going from the C:\Program Files... to D:\Logs
sorry i meant logical file name
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
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')
--Now, copy the file from C:\Program FIles to D:\Logs, and bring the database online again
ALTER DATABASE TRA_100 SET ONLINE
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.
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.
ASKER
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\EVVSMailboxVaultS tore_2LOG. ldf')
-------------------------- -
Query Analyzer gives me an alert that the database is offline.
-------------------------- -
USE EVVSMailboxVaultStore_2
EXEC sp_helpfile
Outputs:
That the EVVSMailboxVaultStore_2LOG 70 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?
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\EVVSMailboxVaultS
--------------------------
Query Analyzer gives me an alert that the database is offline.
--------------------------
USE EVVSMailboxVaultStore_2
EXEC sp_helpfile
Outputs:
That the EVVSMailboxVaultStore_2LOG
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.
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.
RTM2007, did it work for you? any issues? let me know.