Moving a log file while still "attached"

Is there a way to move a log file to another drive while it is still
attached ?
Reason I am asking... I have run into cases where after detaching a
database, I was unable to re-attach it.No, you cant move the primary log file while your DB is up and running. You
can however create a second log file while the DB is still attached (and you
may be running out of space in the primary log file)
Would I create a second log - no unless absolutley necessary.
Next time you try moving the log file via detach and attach - make sure you
run dbcc checkdb before detaching - when no problems are reported - copy the
original log file to its new location - dont move it. This way in case attac
h
shouldnt work (e.g the log file got damaged on its way to the new volume) yo
u
can reattach with minimal further loss of time - to your original log file.
Hope this helped.
"Rob" wrote:

> Is there a way to move a log file to another drive while it is still
> attached ?
> Reason I am asking... I have run into cases where after detaching a
> database, I was unable to re-attach it.
>
>|||No, you cant move the primary log file while your DB is up and running. You
can however create a second log file while the DB is still attached (and you
may be running out of space in the primary log file)
Would I create a second log - no unless absolutley necessary.
Next time you try moving the log file via detach and attach - make sure you
run dbcc checkdb before detaching - when no problems are reported - copy the
original log file to its new location - dont move it. This way in case attac
h
shouldnt work (e.g the log file got damaged on its way to the new volume) yo
u
can reattach with minimal further loss of time - to your original log file.
Hope this helped.
"Rob" wrote:

> Is there a way to move a log file to another drive while it is still
> attached ?
> Reason I am asking... I have run into cases where after detaching a
> database, I was unable to re-attach it.
>
>|||Rob wrote:
> Is there a way to move a log file to another drive while it is still
> attached ?
> Reason I am asking... I have run into cases where after detaching a
> database, I was unable to re-attach it.
>
>
Instead of using attach, you can use RESTORE WITH MOVE. This is a much
safer way to do it since your source db will still be up and running and
operational. This means that if you backup for some reason doesnt work,
you can just make a new backup.
Regards
Steen Schlter Persson
DBA|||> Reason I am asking... I have run into cases where after detaching a database, I was unable
to
> re-attach it.
That should not happen. If you detach a database, then you should be able to
attach it, assuming you
*really* detached it first and you have all the files. If you fulfill those
criteria and still
cannot attach it, you have a bug in the product.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <rwchome@.comcast.net> wrote in message news:77-dncoBVcN_VhPZnZ2dnUVZ_qqdnZ2d@.comcast.c
om...
> Is there a way to move a log file to another drive while it is still attac
hed ?
> Reason I am asking... I have run into cases where after detaching a databa
se, I was unable to
> re-attach it.
>
>|||Rob wrote:
> Is there a way to move a log file to another drive while it is still
> attached ?
> Reason I am asking... I have run into cases where after detaching a
> database, I was unable to re-attach it.
>
>
Instead of using attach, you can use RESTORE WITH MOVE. This is a much
safer way to do it since your source db will still be up and running and
operational. This means that if you backup for some reason doesnt work,
you can just make a new backup.
Regards
Steen Schlter Persson
DBA|||> Reason I am asking... I have run into cases where after detaching a database, I was unable
to
> re-attach it.
That should not happen. If you detach a database, then you should be able to
attach it, assuming you
*really* detached it first and you have all the files. If you fulfill those
criteria and still
cannot attach it, you have a bug in the product.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <rwchome@.comcast.net> wrote in message news:77-dncoBVcN_VhPZnZ2dnUVZ_qqdnZ2d@.comcast.c
om...
> Is there a way to move a log file to another drive while it is still attac
hed ?
> Reason I am asking... I have run into cases where after detaching a databa
se, I was unable to
> re-attach it.
>
>

Moving a log file while still "attached"

Is there a way to move a log file to another drive while it is still
attached ?
Reason I am asking... I have run into cases where after detaching a
database, I was unable to re-attach it.No, you cant move the primary log file while your DB is up and running. You
can however create a second log file while the DB is still attached (and you
may be running out of space in the primary log file)
Would I create a second log - no unless absolutley necessary.
Next time you try moving the log file via detach and attach - make sure you
run dbcc checkdb before detaching - when no problems are reported - copy the
original log file to its new location - dont move it. This way in case attach
shouldnt work (e.g the log file got damaged on its way to the new volume) you
can reattach with minimal further loss of time - to your original log file.
Hope this helped.
"Rob" wrote:
> Is there a way to move a log file to another drive while it is still
> attached ?
> Reason I am asking... I have run into cases where after detaching a
> database, I was unable to re-attach it.
>
>|||Rob wrote:
> Is there a way to move a log file to another drive while it is still
> attached ?
> Reason I am asking... I have run into cases where after detaching a
> database, I was unable to re-attach it.
>
>
Instead of using attach, you can use RESTORE WITH MOVE. This is a much
safer way to do it since your source db will still be up and running and
operational. This means that if you backup for some reason doesnt work,
you can just make a new backup.
--
Regards
Steen Schlüter Persson
DBA|||> Reason I am asking... I have run into cases where after detaching a database, I was unable to
> re-attach it.
That should not happen. If you detach a database, then you should be able to attach it, assuming you
*really* detached it first and you have all the files. If you fulfill those criteria and still
cannot attach it, you have a bug in the product.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <rwchome@.comcast.net> wrote in message news:77-dncoBVcN_VhPZnZ2dnUVZ_qqdnZ2d@.comcast.com...
> Is there a way to move a log file to another drive while it is still attached ?
> Reason I am asking... I have run into cases where after detaching a database, I was unable to
> re-attach it.
>
>sql

Moved SQL to the new box

Hi everybody,

I re-built SQl server 2000 on the new box. Attached and detached all DBs (master, msdb, users DBs) and moved them to the new paths (E:\mssql). Everything is fine.

The questions are:
1. The 'filename' in the master.sysdatabases for 'master' DB is still pointed to the old location (F:\) even physicaly files are on E:\ drive;

2. The 'phyname' in the master.sysdevices for all devices (DBs), including master, msdb, model are still shows the old location (F:\);
At list I will get a wrong reports of the files location.

it could be other places in the system tables that keep old information.
How and where I can correct the information?

3. All jobs, bakups, etc. that creates output files still pointed to the old locations (let say to the F:\sql2000\). F:\ drive is not exists anymore.
I will recreate all jobs and main plans to specify correct path.
But how can I change the DEFAULT locations for all backups, logs, etc. ?

Thank you,
LYOne way you could do it (sorry but its a bit long winded), is to create your dbs on the new server and restore the dbs you wish to move, using the "with move" clause on the resore database command.

By virtue of the fact you have created the dbs on the new server your paths should be OK, and not inherited by the dbs you sp_attach_db'ed

Hope this helps|||In the rebuild situation, I generally will not restore or reattach system databases, only the user databases. Because my past experience of having numerous troubles with the master after restoring to a new machine. Scheduled tasks and DTS can be saved and transfer to the new machine.

Good luck!|||Originally posted by dbabren
One way you could do it (sorry but its a bit long winded), is to create your dbs on the new server and restore the dbs you wish to move, using the "with move" clause on the resore database command.

By virtue of the fact you have created the dbs on the new server your paths should be OK, and not inherited by the dbs you sp_attach_db'ed

Hope this helps

=====

All user DB's are fine.
The only incorrect information for master.sysdatabases.filename (for master database) and master.sysdevices (for all devices).

Thank you.
LYsql