Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers 4

up vote 0 down vote accepted

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.

share|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
    
thanks gbn, I added an edit. –  user13734 Jul 24 '09 at 12:56
    
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
show 1 more comment

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

share|improve this answer
add comment

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

share|improve this answer
add comment

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

share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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