SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best way to move the log file to a different drive


Best way to move the log file to a different drive

Author
Message
Maximus-452684
Maximus-452684
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 140
I need to move the log file for a database to a different drive, what is best way to do this? i was going to use detach\attach method
dajonx
dajonx
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6025 Visits: 1414
Please verify this, but I believe you can do a modify file to the new location, then do a restart on SQL service via the Configuration Manager.

My Blog!
Shawn Melton
Shawn Melton
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21048 Visits: 3624
dajonx (4/22/2011)
Please verify this, but I believe you can do a modify file to the new location, then do a restart on SQL service via the Configuration Manager.

If you just want to move one database there is no reason to take down the whole SQL instance to do it.

The detach\attach method is the best method if you just need to move it for one database.

Shawn Melton
Twitter: @wsmelton
Blog: blog.wsmelton.info
dajonx
dajonx
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6025 Visits: 1414
Thanks for correcting my previous "answer"! :-D

My Blog!
Maximus-452684
Maximus-452684
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 140
Awesome, thanks.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)

Group: General Forum Members
Points: 859524 Visits: 48576
Shawn Melton (4/22/2011)
The detach\attach method is the best method if you just need to move it for one database.


Disagree...

You can move a database file using ALTER DATABASE, then take just that database offline, move the file, then bring the DB back online. Better solution if there's CDC, replication or other such things configured.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeffrey Williams 3188
Jeffrey Williams 3188
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72930 Visits: 10753
I prefer using ALTER DATABASE ... MODIFY FILE to move database files. This method preserves the database ownership and does not require a detach/attach of the database.

The first step is to modify the file location:

ALTER DATABASE {your database} MODIFY FILE (filename = {new file location and name});
GO

Then, you take the database offline:

ALTER DATABASE {your database} SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

Once the database is offline, copy the file to the new location and name and then bring the database online:

ALTER DATABASE {your database} SET ONLINE;
GO

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

dajonx
dajonx
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6025 Visits: 1414
Ahh, that's what it was... OFFLINE/ONLINE after the modify file... Thanks!

My Blog!
Steve T
Steve T
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1453 Visits: 359
If you're just moving a secondary log file...

1. create a new log file in the new location.
2. Do a shrinkfile with EMPTYFILE option on the log file that is to be moved.
3. Delete the old log file.

This does not work with the primary log file(the inital one that gets created). Even though the MSDN seems to imply that you can. See http://msdn.microsoft.com/en-us/library/ms191433.aspx. You will get a error 5020: The primary data or log file cannot be removed...



doodlingdba
doodlingdba
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1841 Visits: 656
I know last post was 6 years ago Smile

Just wanted to clarify that if using the ALTER DATABASE MODIFY FILE method, then taking the db offline as some of the posters have described above, you must COPY the file to a new location, not MOVE it, or you will get an error. This is my script:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'TestLogShip');

USE master; --do this all from the master
ALTER DATABASE TestLogShip
MODIFY FILE (name='TestLogShip'
,filename='E:\MSSQL\TestLogShip.mdf'); --Filename is new location

ALTER DATABASE TestLogShip
MODIFY FILE (name='TestLogShip_log'
,filename='E:\DBA_TEST\TestLogShip_log.ldf'); --Filename is new location

ALTER DATABASE TestLogShip SET OFFLINE WITH ROLLBACK IMMEDIATE

--- COPY the physical files !!!

ALTER DATABASE TestLogShip SET ONLINE;

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'TestLogShip');
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search