Moving a log file

Hi,

I am trying to move a log file in SQL2005.

I have tried unattaching a DB, then reattaching it and giving it a path to a new log file but it refuses to mount the database and simply reconnects to the old log file.

if i delete the old log file then it recreats it in the old path.

Any one know what i am doing worng.

There are several ways to handle this.

When you attach the database, the server looks in the db file to determine where the transaction log file is located. It will then display the log file and location -you can change the log file location at this step BEFORE you confirm the ATTACH.

You can also use T-SQL, DETACH and ATTACH, taking care to specify where to find the log file. See Books Online for syntax specifics.

You could do a BACKUP and RESTORE, using the WITH MOVE options for RESTORE. Again, check Books Online for syntax specifics.

|||

Check Create database with ATTACH_REBUILD_LOG option...

Check BOL for more details.

http://msdn2.microsoft.com/en-us/library/ms176061.aspx

|||

Follow as Arnie suggested tomove the file before using SP_ATTACH_DB, you could use SP_ATTACH_SINGLE_FILE_DB in this case that will recreate fresh log file.

The ATTACH_REBUILD_LOG clause enables attaching a database without requiring all of the log files. For example, when detaching a database from a production server for use as a read-only database on a reporting server, the read-only environment will not require all of the log files used in production. ATTACH_REBUILD_LOG lets you copy the database to the reporting server without having to copy over all of the production log files.

|||

Thanks guys

that worked, I dettached the DB moved the Log file then re-attached it using the create command and the ATTACH_REBUILD_LOG and specifed teh location of the log file and it worked.