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

move table from SQL2000 to SQL2005?

I need to move some tables from an SQL2000 database on one server to an
SQL2005 database on another server(and in some cases back again). I don't
have enterprise manager just SQL Manager Express which doesn't play nice with
SQL2000.
Any suggestions would be appreciated. T-SQL solutions would be preferred
because they're free ;)
Thanks!
Hi
"Dabbler" wrote:

> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
If you don't want to re-create the table definition (which you would have to
script and run through SQLCMD if you did!) then you can populate the table
using BCP or if you have a linked server INSERT...SELECT
John
|||I don't think I can use BCP because both servers are hosted at Appliedi.net
so I don't have access to their file systems.
Is there a way to simultaneously connect to two databases on two different
servers with T-SQL? that would allow me to use your "linked server"
suggestion.
Thanks much!
Michael
"John Bell" wrote:

> Hi
> "Dabbler" wrote:
>
> If you don't want to re-create the table definition (which you would have to
> script and run through SQLCMD if you did!) then you can populate the table
> using BCP or if you have a linked server INSERT...SELECT
> John
|||Hi
"Dabbler" wrote:
[vbcol=seagreen]
> I don't think I can use BCP because both servers are hosted at Appliedi.net
> so I don't have access to their file systems.
> Is there a way to simultaneously connect to two databases on two different
> servers with T-SQL? that would allow me to use your "linked server"
> suggestion.
> Thanks much!
> Michael
> "John Bell" wrote:
Use sp_addlinkedserver to create the linked server and use either OPENQUERY
or four part names to run the query on the linked server.
John
|||Thanks John, that's the clue I needed.
"John Bell" wrote:

> Hi
> "Dabbler" wrote:
>
> Use sp_addlinkedserver to create the linked server and use either OPENQUERY
> or four part names to run the query on the linked server.
> John
|||On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
I noticed you mentioned that you don't have SQL Server Management
Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
the tools come with the DVD. If you used the tool, you could simply
use the copy database wizard to achieve these results. If you wanted
to do another method, you could simply utilize the backup and restore
method using your normal T-SQL methods. Simply backup your 2000
database and restore it on your 2005 database.
Again, I might be missing something, but wanted to assist you in any
way possible.
Aaron
|||Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
SQL Server 2005 Express. I only have SSMS Express which is lite version of
SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
but of course the install blocks because of my SQL Server Express install. Of
course all I really need is Enterprise Manager but I'm not an Enterprise,
just an independent developer ;)
"acorcoran" wrote:

> On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I noticed you mentioned that you don't have SQL Server Management
> Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
> the tools come with the DVD. If you used the tool, you could simply
> use the copy database wizard to achieve these results. If you wanted
> to do another method, you could simply utilize the backup and restore
> method using your normal T-SQL methods. Simply backup your 2000
> database and restore it on your 2005 database.
> Again, I might be missing something, but wanted to assist you in any
> way possible.
> Aaron
>
|||Hi
"Dabbler" wrote:

> Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
> SQL Server 2005 Express. I only have SSMS Express which is lite version of
> SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
> but of course the install blocks because of my SQL Server Express install. Of
> course all I really need is Enterprise Manager but I'm not an Enterprise,
> just an independent developer ;)
>
You may want to consider buying yourself a copy of the developer edition
which is about $50 even if your deployments are on the Express, although for
your current issue it may not help.
John
|||Thanks John... I will get DEV, just have been putting it off, especially
since I'm concerned about the memory footprint of full SQL2005 vs express
edition. In the meantime I've installed the client tools from the trial
version which should hold me till I win the lottery ;)
"John Bell" wrote:

> Hi
> "Dabbler" wrote:
>
> You may want to consider buying yourself a copy of the developer edition
> which is about $50 even if your deployments are on the Express, although for
> your current issue it may not help.
> John
|||Hi
"Dabbler" wrote:

> Thanks John... I will get DEV, just have been putting it off, especially
> since I'm concerned about the memory footprint of full SQL2005 vs express
> edition. In the meantime I've installed the client tools from the trial
> version which should hold me till I win the lottery ;)
>
For $50 you won't need all the numbers!! Using the tools should prove that
it is excellent value!
John

move table from SQL2000 to SQL2005?

I need to move some tables from an SQL2000 database on one server to an
SQL2005 database on another server(and in some cases back again). I don't
have enterprise manager just SQL Manager Express which doesn't play nice wit
h
SQL2000.
Any suggestions would be appreciated. T-SQL solutions would be preferred
because they're free ;)
Thanks!Hi
"Dabbler" wrote:

> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice w
ith
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
If you don't want to re-create the table definition (which you would have to
script and run through SQLCMD if you did!) then you can populate the table
using BCP or if you have a linked server INSERT...SELECT
John|||I don't think I can use BCP because both servers are hosted at Appliedi.net
so I don't have access to their file systems.
Is there a way to simultaneously connect to two databases on two different
servers with T-SQL? that would allow me to use your "linked server"
suggestion.
Thanks much!
Michael
"John Bell" wrote:

> Hi
> "Dabbler" wrote:
>
> If you don't want to re-create the table definition (which you would have
to
> script and run through SQLCMD if you did!) then you can populate the table
> using BCP or if you have a linked server INSERT...SELECT
> John|||Hi
"Dabbler" wrote:
[vbcol=seagreen]
> I don't think I can use BCP because both servers are hosted at Appliedi.ne
t
> so I don't have access to their file systems.
> Is there a way to simultaneously connect to two databases on two different
> servers with T-SQL? that would allow me to use your "linked server"
> suggestion.
> Thanks much!
> Michael
> "John Bell" wrote:
>
Use sp_addlinkedserver to create the linked server and use either OPENQUERY
or four part names to run the query on the linked server.
John|||Thanks John, that's the clue I needed.
"John Bell" wrote:

> Hi
> "Dabbler" wrote:
>
> Use sp_addlinkedserver to create the linked server and use either OPENQUER
Y
> or four part names to run the query on the linked server.
> John|||On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice w
ith
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
I noticed you mentioned that you don't have SQL Server Management
Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
the tools come with the DVD. If you used the tool, you could simply
use the copy database wizard to achieve these results. If you wanted
to do another method, you could simply utilize the backup and restore
method using your normal T-SQL methods. Simply backup your 2000
database and restore it on your 2005 database.
Again, I might be missing something, but wanted to assist you in any
way possible.
Aaron|||Ya, the DVD you're thinking of is full SQL Server 2005 but I have download o
f
SQL Server 2005 Express. I only have SSMS Express which is lite version of
SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
but of course the install blocks because of my SQL Server Express install. O
f
course all I really need is Enterprise Manager but I'm not an Enterprise,
just an independent developer ;)
"acorcoran" wrote:

> On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I noticed you mentioned that you don't have SQL Server Management
> Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
> the tools come with the DVD. If you used the tool, you could simply
> use the copy database wizard to achieve these results. If you wanted
> to do another method, you could simply utilize the backup and restore
> method using your normal T-SQL methods. Simply backup your 2000
> database and restore it on your 2005 database.
> Again, I might be missing something, but wanted to assist you in any
> way possible.
> Aaron
>|||Hi
"Dabbler" wrote:

> Ya, the DVD you're thinking of is full SQL Server 2005 but I have download
of
> SQL Server 2005 Express. I only have SSMS Express which is lite version of
> SSMS. I'm trying to figure out how to install the full SQL Server 2005 tri
al
> but of course the install blocks because of my SQL Server Express install.
Of
> course all I really need is Enterprise Manager but I'm not an Enterprise,
> just an independent developer ;)
>
You may want to consider buying yourself a copy of the developer edition
which is about $50 even if your deployments are on the Express, although for
your current issue it may not help.
John|||Thanks John... I will get DEV, just have been putting it off, especially
since I'm concerned about the memory footprint of full SQL2005 vs express
edition. In the meantime I've installed the client tools from the trial
version which should hold me till I win the lottery ;)
"John Bell" wrote:

> Hi
> "Dabbler" wrote:
>
> You may want to consider buying yourself a copy of the developer edition
> which is about $50 even if your deployments are on the Express, although f
or
> your current issue it may not help.
> John|||Hi
"Dabbler" wrote:

> Thanks John... I will get DEV, just have been putting it off, especially
> since I'm concerned about the memory footprint of full SQL2005 vs express
> edition. In the meantime I've installed the client tools from the trial
> version which should hold me till I win the lottery ;)
>
For $50 you won't need all the numbers!! Using the tools should prove that
it is excellent value!
John

move table from SQL2000 to SQL2005?

I need to move some tables from an SQL2000 database on one server to an
SQL2005 database on another server(and in some cases back again). I don't
have enterprise manager just SQL Manager Express which doesn't play nice with
SQL2000.
Any suggestions would be appreciated. T-SQL solutions would be preferred
because they're free ;)
Thanks!Hi
"Dabbler" wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
If you don't want to re-create the table definition (which you would have to
script and run through SQLCMD if you did!) then you can populate the table
using BCP or if you have a linked server INSERT...SELECT
John|||I don't think I can use BCP because both servers are hosted at Appliedi.net
so I don't have access to their file systems.
Is there a way to simultaneously connect to two databases on two different
servers with T-SQL? that would allow me to use your "linked server"
suggestion.
Thanks much!
Michael
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
> > I need to move some tables from an SQL2000 database on one server to an
> > SQL2005 database on another server(and in some cases back again). I don't
> > have enterprise manager just SQL Manager Express which doesn't play nice with
> > SQL2000.
> >
> > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > because they're free ;)
> >
> > Thanks!
> If you don't want to re-create the table definition (which you would have to
> script and run through SQLCMD if you did!) then you can populate the table
> using BCP or if you have a linked server INSERT...SELECT
> John|||Hi
"Dabbler" wrote:
> I don't think I can use BCP because both servers are hosted at Appliedi.net
> so I don't have access to their file systems.
> Is there a way to simultaneously connect to two databases on two different
> servers with T-SQL? that would allow me to use your "linked server"
> suggestion.
> Thanks much!
> Michael
> "John Bell" wrote:
> > Hi
> >
> > "Dabbler" wrote:
> >
> > > I need to move some tables from an SQL2000 database on one server to an
> > > SQL2005 database on another server(and in some cases back again). I don't
> > > have enterprise manager just SQL Manager Express which doesn't play nice with
> > > SQL2000.
> > >
> > > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > > because they're free ;)
> > >
> > > Thanks!
> >
> > If you don't want to re-create the table definition (which you would have to
> > script and run through SQLCMD if you did!) then you can populate the table
> > using BCP or if you have a linked server INSERT...SELECT
> >
> > John
Use sp_addlinkedserver to create the linked server and use either OPENQUERY
or four part names to run the query on the linked server.
John|||Thanks John, that's the clue I needed.
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
> > I don't think I can use BCP because both servers are hosted at Appliedi.net
> > so I don't have access to their file systems.
> >
> > Is there a way to simultaneously connect to two databases on two different
> > servers with T-SQL? that would allow me to use your "linked server"
> > suggestion.
> >
> > Thanks much!
> >
> > Michael
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > "Dabbler" wrote:
> > >
> > > > I need to move some tables from an SQL2000 database on one server to an
> > > > SQL2005 database on another server(and in some cases back again). I don't
> > > > have enterprise manager just SQL Manager Express which doesn't play nice with
> > > > SQL2000.
> > > >
> > > > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > > > because they're free ;)
> > > >
> > > > Thanks!
> > >
> > > If you don't want to re-create the table definition (which you would have to
> > > script and run through SQLCMD if you did!) then you can populate the table
> > > using BCP or if you have a linked server INSERT...SELECT
> > >
> > > John
> Use sp_addlinkedserver to create the linked server and use either OPENQUERY
> or four part names to run the query on the linked server.
> John|||On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
I noticed you mentioned that you don't have SQL Server Management
Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
the tools come with the DVD. If you used the tool, you could simply
use the copy database wizard to achieve these results. If you wanted
to do another method, you could simply utilize the backup and restore
method using your normal T-SQL methods. Simply backup your 2000
database and restore it on your 2005 database.
Again, I might be missing something, but wanted to assist you in any
way possible.
Aaron|||Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
SQL Server 2005 Express. I only have SSMS Express which is lite version of
SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
but of course the install blocks because of my SQL Server Express install. Of
course all I really need is Enterprise Manager but I'm not an Enterprise,
just an independent developer ;)
"acorcoran" wrote:
> On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> > I need to move some tables from an SQL2000 database on one server to an
> > SQL2005 database on another server(and in some cases back again). I don't
> > have enterprise manager just SQL Manager Express which doesn't play nice with
> > SQL2000.
> >
> > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > because they're free ;)
> >
> > Thanks!
> I noticed you mentioned that you don't have SQL Server Management
> Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
> the tools come with the DVD. If you used the tool, you could simply
> use the copy database wizard to achieve these results. If you wanted
> to do another method, you could simply utilize the backup and restore
> method using your normal T-SQL methods. Simply backup your 2000
> database and restore it on your 2005 database.
> Again, I might be missing something, but wanted to assist you in any
> way possible.
> Aaron
>|||Hi
"Dabbler" wrote:
> Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
> SQL Server 2005 Express. I only have SSMS Express which is lite version of
> SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
> but of course the install blocks because of my SQL Server Express install. Of
> course all I really need is Enterprise Manager but I'm not an Enterprise,
> just an independent developer ;)
>
You may want to consider buying yourself a copy of the developer edition
which is about $50 even if your deployments are on the Express, although for
your current issue it may not help.
John|||Thanks John... I will get DEV, just have been putting it off, especially
since I'm concerned about the memory footprint of full SQL2005 vs express
edition. In the meantime I've installed the client tools from the trial
version which should hold me till I win the lottery ;)
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
> > Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
> > SQL Server 2005 Express. I only have SSMS Express which is lite version of
> > SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
> > but of course the install blocks because of my SQL Server Express install. Of
> > course all I really need is Enterprise Manager but I'm not an Enterprise,
> > just an independent developer ;)
> >
> You may want to consider buying yourself a copy of the developer edition
> which is about $50 even if your deployments are on the Express, although for
> your current issue it may not help.
> John|||Hi
"Dabbler" wrote:
> Thanks John... I will get DEV, just have been putting it off, especially
> since I'm concerned about the memory footprint of full SQL2005 vs express
> edition. In the meantime I've installed the client tools from the trial
> version which should hold me till I win the lottery ;)
>
For $50 you won't need all the numbers!! Using the tools should prove that
it is excellent value!
John