Moving The Transaction Log file location

Rob wrote:
> Is there a way to safely move the transaction log file ? Tried to detach
> the db, move the transaction log file, then tried to re-attach the databas
e
> with a the modified transaction log path, but that was not successful.
> Thanks
>
There should be no reason that wouldn't work. What error message did
you get? I'm sure it was something other than "not successful".Thanks Tracy,
I should have taken a closer look... I was simply testing the process on
the Pubs db at the time...
The error message stated something to the effect that the transaction log
file was not correct and that a new one would be created... which it did
do.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ukMM34hlGHA.2180@.TK2MSFTNGP05.phx.gbl...
> Rob wrote:
> There should be no reason that wouldn't work. What error message did you
> get? I'm sure it was something other than "not successful".|||Rob wrote:
> Thanks Tracy,
> I should have taken a closer look... I was simply testing the process on
> the Pubs db at the time...
> The error message stated something to the effect that the transaction log
> file was not correct and that a new one would be created... which it did
> do.
>
You still shouldn't have gotten an error, if you were indeed attaching
the correct log file. It is trivial to detach a database file or log
file, move to a new location, and then reattach.|||Rob wrote:
> Thanks Tracy,
> I should have taken a closer look... I was simply testing the process on
> the Pubs db at the time...
> The error message stated something to the effect that the transaction log
> file was not correct and that a new one would be created... which it did
> do.
>
Are you sure you ran a sp_attach_db and not sp_attach_single_file_db?
I'm not sure though but I'd assume that sp_attach_db wouldn't create a
new logfile.
Another work around could also be to use RESTORE DATABASE with the MOVE
option. You could restore to a database with another name and once it
was succeded you could delete the old one and then rename the new one.
It might be a bit more cumbersome but it's a safer method since you'll
always have a running version of the database. When you use sp_detach_db
you haven't even got your source db running in case the sp_attach_db fails.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Is there a way to safely move the transaction log file ? Tried to detach
the db, move the transaction log file, then tried to re-attach the database
with a the modified transaction log path, but that was not successful.
Thanks|||Rob wrote:
> Is there a way to safely move the transaction log file ? Tried to detach
> the db, move the transaction log file, then tried to re-attach the databas
e
> with a the modified transaction log path, but that was not successful.
> Thanks
>
There should be no reason that wouldn't work. What error message did
you get? I'm sure it was something other than "not successful".|||Thanks Tracy,
I should have taken a closer look... I was simply testing the process on
the Pubs db at the time...
The error message stated something to the effect that the transaction log
file was not correct and that a new one would be created... which it did
do.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ukMM34hlGHA.2180@.TK2MSFTNGP05.phx.gbl...
> Rob wrote:
> There should be no reason that wouldn't work. What error message did you
> get? I'm sure it was something other than "not successful".|||Rob wrote:
> Thanks Tracy,
> I should have taken a closer look... I was simply testing the process on
> the Pubs db at the time...
> The error message stated something to the effect that the transaction log
> file was not correct and that a new one would be created... which it did
> do.
>
You still shouldn't have gotten an error, if you were indeed attaching
the correct log file. It is trivial to detach a database file or log
file, move to a new location, and then reattach.|||Rob wrote:
> Thanks Tracy,
> I should have taken a closer look... I was simply testing the process on
> the Pubs db at the time...
> The error message stated something to the effect that the transaction log
> file was not correct and that a new one would be created... which it did
> do.
>
Are you sure you ran a sp_attach_db and not sp_attach_single_file_db?
I'm not sure though but I'd assume that sp_attach_db wouldn't create a
new logfile.
Another work around could also be to use RESTORE DATABASE with the MOVE
option. You could restore to a database with another name and once it
was succeded you could delete the old one and then rename the new one.
It might be a bit more cumbersome but it's a safer method since you'll
always have a running version of the database. When you use sp_detach_db
you haven't even got your source db running in case the sp_attach_db fails.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||alter database <dbName> modify file (name=<dbName_log>, filename =
'path_to_new_location')
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Rob" <rwchome@.comcast.net> wrote in message
news:T4CdnQz5gZ9iVgfZnZ2dnUVZ_uydnZ2d@.co
mcast.com...
> Is there a way to safely move the transaction log file ? Tried to detach
> the db, move the transaction log file, then tried to re-attach the
> database with a the modified transaction log path, but that was not
> successful.
> Thanks
>