January 9, 2002 at 11:32 am
Recently it was brought to my attention that I should not have the db and transaction log stored on the same physical disk (for recovery reasons). Is there any easy way of changing the transaction log location beside dropping the database and recreating it with the devices in the desired locations?
January 9, 2002 at 11:40 am
You can detach the database and then reattach with the new locations. See BOL for reference in T-SQL or EM if SQL 2000 (look up sp_detach_db and sp_attach_db)
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 9, 2002 at 11:45 am
Sorry, didn't read the full description this is 6.5. I believe the sp_detach_db solution should still work.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 9, 2002 at 1:02 pm
Thanks for the reply but I don't believe 6.5 has the sp_detach_db procedure. I will be upgrading to 2000 in a few months, perhaps it would be best to wait.
January 9, 2002 at 1:57 pm
You can manually change this in the system tables, but it is not recommended. Personally I'd backup the db, drop it, recreate it as needed and restore.
Steve Jones
January 9, 2002 at 2:02 pm
Ok, I will plan on dropping the db then. I just get a sick feeling in my stomach and my legs get weak anytime I delete a production db even if it is backed up. Thanks for the reply.
January 9, 2002 at 2:08 pm
you are welcome. It's better than editing system tables.
BTW, be sure you track you login/user mappings in case you need to manually fix this.
Steve Jones
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply