Obviously you can detach the whole dbs and reattach using sp_attach and sp_detach. Is there an online way to do this, have had a quick google but can't see anything.


If you want to move the log file without having to take a database offline there is a way I used before. I just hope I remember it correctly:

  1. Add a new log file in the new location with something like:

    ALTER DATABASE foo ADD LOG FILE (NAME=NewLog, FILENAME='YourNewPhysicalPath', SIZE=100MB, other options ...);

  2. Empty the old log file with:


  3. Take log backup with BACKUP LOG foo...

  4. Remove the old log file with:


If the last one says you can not remove because the file is not empty, try executing 2 and 3 again and then 4.

EDIT: Well, it turns out that I have not remebered it correctly... IT WILL NOT WORK this way and as gbn pointed out you can not "empty" the logfile via SHRINKFILE (but it was not my goal anyway - I just wanted to mark the file as "not for use" so it is not reused and can be dropped later). I think a similar technique is still possible for any additional logfiles you have apart from the first one but I will post about it when I get a chance to research that.

| improve this answer | |
  • You can't "empty" log files like you can data files... – gbn Jul 22 '09 at 18:22
  • ..and MS say to use attach/detach support.microsoft.com/kb/224071 – gbn Jul 22 '09 at 18:30
  • 2
    Why has this got up votes? Has anyone tried this with a log file? It won't work! – gbn Jul 24 '09 at 3:52
  • As the KB article indicates, in 2005 the best way is to ALTER DATABASE, not detach/attach. Along those lines, sp_attach_db is now deprecated for 2005/2008. CREATE DATABASE is now preferred since it now has the FOR ATTACH option. – K. Brian Kelley Jul 24 '09 at 13:15
  • Wouldn't this work if you added the second file to the t-log filegroup, set the original file to "cease using", then waiting for the transactions in that file to be aged out? (The time required would depend on activity, and database recovery mode.) I've never done it, but from what I've read this should be possible. – Philip Kelley Jul 24 '09 at 14:19

The best way, which will require some down time, is to use ALTER DATABASE ... SET OFFLINE. There are several good reasons for this.

  1. The database owner is not changed. This can have implications with respect to implied permissions and cross database ownership chaining.
  2. Configurations like FILESTREAM (in 2008) or database specific configuration (like cross database ownership chaining) are not lost.
  3. The database is not re-attached using the wrong name. Fat fingering still happens.

More here:

How to move a SQL Server database within the same instance without losing settings

| improve this answer | |

Sorry, there is no "safe" on-line method.

You have to take the database off-line or detach it.

You can not "empty" a log file like you can a data file.

MS KB 224071

| improve this answer | |

You could do a backup, then restore the backup. During the restore you can specify a different location for the transaction log.

| improve this answer | |
  • Yes I've cheated like that before! I'm not sure when I can take this dbs off-line and a backup and restore is definitely off-line. – alimack Jul 22 '09 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.