mnmorency
asked on
SQL 2000 Transaction Log: How to move JUST the log file of a database to a new drive
I have SQL 2000. Currently the data file and the transaction log file for a database are located on the same drive. I just added a new drive to the system and I would like JUST the transaction log for the database moved to the new drive. I have tried the following:
1. Detach the databse
2. Physically MOVE the .ldf file to the new drive
3. Try to attach the databse (and I get an error that it can't find the transaction log file.)
Then I tried the same steps again but instead I made a COPY of the .ldf file instead of moving it. I could then attach the database, go into the properties screen, select the "Transaction Log" tab, and try to point it to the new location. This also did not work (sorry, I do not have the error message with me right now.)
Do I even have to move the current transaction log file, or can I just have the database create a new .ldf file on the new drive, make it the primary log file, and then delete the other one? If so, what is the best way to do this?
Thanks, Mark
1. Detach the databse
2. Physically MOVE the .ldf file to the new drive
3. Try to attach the databse (and I get an error that it can't find the transaction log file.)
Then I tried the same steps again but instead I made a COPY of the .ldf file instead of moving it. I could then attach the database, go into the properties screen, select the "Transaction Log" tab, and try to point it to the new location. This also did not work (sorry, I do not have the error message with me right now.)
Do I even have to move the current transaction log file, or can I just have the database create a new .ldf file on the new drive, make it the primary log file, and then delete the other one? If so, what is the best way to do this?
Thanks, Mark
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if you don't have replication may be you can take a backup of database and while restoring specify the new drive path for the log file.
if you attach Database from EM (enterprise Manager)
type there new path forthe log file...
type there new path forthe log file...
ASKER
AMIT G: Your solution worked perfectly when I tried it on one of the databases. However, now I have a new problem:
I continued to move the rest of the user databases and now my SQL Service won't start. In the event viewer I am getting the following error:
Error: 9003 Severity: 20 State: 1
The next message I get in the event viewer is:
Cannot recover the master database. Exiting.
I am guessing my master database got corrupt somehow... :( I tried starting a sigle user instance of SQL from the command prompt (sqlservr.exe -c -m) and again, same error message: Cannot recover the master database. Exiting.
What can I do at this point to fix this?
I continued to move the rest of the user databases and now my SQL Service won't start. In the event viewer I am getting the following error:
Error: 9003 Severity: 20 State: 1
The next message I get in the event viewer is:
Cannot recover the master database. Exiting.
I am guessing my master database got corrupt somehow... :( I tried starting a sigle user instance of SQL from the command prompt (sqlservr.exe -c -m) and again, same error message: Cannot recover the master database. Exiting.
What can I do at this point to fix this?
Just a guess..the common procedure sp_detach_db/ sp_attach_db for moving data/log/extension files works ONLY with USER databases..SYSTEM databases (master, msdb, model, tempdb) require a specific procedures...
Did you do anything with the master database logfiles also?
ASKER
AMit G: I made a copy of all the *.ldf files in the directory, including the master db log file and copied them into another directory to make sure I had copies in case anything went wrong... that's about it.
<<AMit G: I made a copy of all the *.ldf files in the directory, including the master db log file and copied them into another directory to make sure I had copies in case anything went wrong... that's about it.>>Did you try to detach/attach directly the master db then this is your problem...
Follow the procedure I have given you else it won't work..If you already tried a detach on the master then you will need to remodify the startup parameters else SQL Server won't start again...
So you did not move the logfile of mater database. You just made a copy. You never did sp_detach/sp_attach for the master database. Is that correct?
ASKER
amit g:
I did not do any detach for the master database. And I did just copy the *ldf files.
I did physically MOVE the ldf files for the user databases. Say I accidentally MOVED the ldf file for master-- would that have this effect? When i started to have problems, I moved all the ldf files back to the original location, so it is *possible* this could have happened without me knowing now, though unlikely.
I did not do any detach for the master database. And I did just copy the *ldf files.
I did physically MOVE the ldf files for the user databases. Say I accidentally MOVED the ldf file for master-- would that have this effect? When i started to have problems, I moved all the ldf files back to the original location, so it is *possible* this could have happened without me knowing now, though unlikely.
You could not have moved mastlog.ldf unless the SQL server was stopped. Could it have happened? Either way you are going to repair the database. Try steps in
http://blog.visionpace.com/2005/08/how_to_recover_.html
and post any errors you get.
http://blog.visionpace.com/2005/08/how_to_recover_.html
and post any errors you get.