Moving DB Log destination?

Question

I currently have SQL DB and Log files going to the same drive. I would like to move the Log files to their own drive. Is there an easy way to achieve this and will it require downtime/reboot of the server?

• Sir Swears-a-lot: Instructions from Microsoft. msdn.microsoft.com/en-nz/library/ms345408(v=sql.130).aspx

Answer 1

Please don't detach and attach your database. If you detach it and something happens to it, you now have zero copies of your database. Much safer approaches:

---

Backup/Restore

USE master;
GO
BACKUP DATABASE floob TO DISK = 'c:\wherever\floob.bak' WITH INIT, COPY_ONLY;
ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE floob FROM DISK = 'c:\wherever\floob.bak'
 WITH REPLACE, 
 MOVE N'floob_log' TO 'c:\new_path\floob.ldf';

---

Offline/Online

(You should still take a backup prior to doing this.)

USE master;
GO
ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE floob MODIFY FILE 
 (name = N'floob_log', filename = 'c:\new_path\floob.ldf');
ALTER DATABASE floob SET OFFLINE;

Now go to Windows Explorer, copy the ldf file to the new location, and rename the original. Make sure the SQL Server service account has adequate permissions on the new folder. Then:

ALTER DATABASE floob SET ONLINE;
ALTER DATABASE floob SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Once the database is back up and running, you can safely delete the log file from the source location.

---

Both methods will need a brief downtime (how much depends on a lot of factors, including size of the database and speed of I/O both on the drives themselves and whatever path is between them), but neither will require you to reboot the server.