Home / server / Questions / 44014
Accepted
alimack
Asked: 2009-07-23 05:28:19 +0800 CST

Best way to move a log file in SQL Server 2005

  • 772

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.

sql-server-2005 log-files

4 Answers

  1. 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

    • 4

  2. 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

    • 2

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

    • 1
  4. Best Answer

    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:

      USE foo; DBCC SHRINKFILE(OldLog, EMPTYFILE);

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

    4. Remove the old log file with:

      ALTER DATABASE foo REMOVE FILE OldLog;

    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.

    • 0