Solved

How to move LDF file to another drive for already created database?

Posted on 2011-02-17
3
605 Views
Last Modified: 2012-05-11
I have a 23GB database MDF and 1GB database LDF located in E:\SQLServer.

I understand that if I shift my LDF file to another hard disk, eg. D:, the performance might be slightly faster.

I tried to detach the database and delete the LDF file. When I reattach the MDF, SQL2000 will re-create the LDF file back in E:\SQLServer, but I wish to move the LDF file to D:\, which is another hard disk. How do I do this?

Thank you.
0
Comment
Question by:cq27
3 Comments
 
LVL 12

Expert Comment

by:mcv22
Comment Utility

ALTER DATABASE DatabaseName MODIFY FILE (NAME = Database_Log, FILENAME = 'D:\SQLServer\Database_log.ldf')

Open in new window

0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 125 total points
Comment Utility
hi

why did you delete the LDF?

use master
GO
   sp_detach_db 'YOURDATABASE'
   go
   sp_attach_db 'YOURDB','E:\Sqldata\DATAFILE.mdf','D:\Sqldata\LOGFILE.ldf'
   go

0
 

Author Closing Comment

by:cq27
Comment Utility
Thanks for your solution. It is the easiest to understand. I tried to follow others' instructions but no one seem to be as clear as yours.

I have tried it on a test database and it works. Thank you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now