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.

Moving a database to a new drive

I have a CRM 3.0 application running on a Windows server 2003 R2 and SQL
2005. I need to move the database and log file to a different drive from the
current location. It is currently on C: which is the system partion. I need
to move the log file and data file to H and I respectively.
The database has one filegroup, Primary. It has one datafile in the primary
filegroup, and one log file. I need to move these files to drives H and I
respectively.
Will someone be able to point me in the right direction on how to move this
files?
Thanks in advance.
ODINF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/defaul...b;EN-US;q224071
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"OD" <oludan@.hotmail.com> wrote in message
news:%238v5kKd0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I have a CRM 3.0 application running on a Windows server 2003 R2 and SQL
>2005. I need to move the database and log file to a different drive from
>the current location. It is currently on C: which is the system partion. I
>need to move the log file and data file to H and I respectively.
> The database has one filegroup, Primary. It has one datafile in the
> primary filegroup, and one log file. I need to move these files to drives
> H and I respectively.
> Will someone be able to point me in the right direction on how to move
> this files?
> Thanks in advance.
> OD
>|||See reply in sqlserver.tools
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"OD" <oludan@.hotmail.com> wrote in message
news:%238v5kKd0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I have a CRM 3.0 application running on a Windows server 2003 R2 and SQL
>2005. I need to move the database and log file to a different drive from
>the current location. It is currently on C: which is the system partion. I
>need to move the log file and data file to H and I respectively.
> The database has one filegroup, Primary. It has one datafile in the
> primary filegroup, and one log file. I need to move these files to drives
> H and I respectively.
> Will someone be able to point me in the right direction on how to move
> this files?
> Thanks in advance.
> OD
>|||Thanks Jasper. You have provided the exact information that I am searching.
For some reason I couldn't find it on microsoft's Web site.
Thanks again,
OD
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:e7LOZPd0GHA.720@.TK2MSFTNGP02.phx.gbl...
> See reply in sqlserver.tools
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:%238v5kKd0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>|||You could take a look at KBArticle
http://msdn2.microsoft.com/en-us/library/ms187858.aspx
Thanks
Sethu Srinivasan, Software Design Engineer, SQL Server Manageability
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"OD" <oludan@.hotmail.com> wrote in message
news:%238v5kKd0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I have a CRM 3.0 application running on a Windows server 2003 R2 and SQL
>2005. I need to move the database and log file to a different drive from
>the current location. It is currently on C: which is the system partion. I
>need to move the log file and data file to H and I respectively.
> The database has one filegroup, Primary. It has one datafile in the
> primary filegroup, and one log file. I need to move these files to drives
> H and I respectively.
> Will someone be able to point me in the right direction on how to move
> this files?
> Thanks in advance.
> OD
>

Moved SQL DB to SQL2005 and it is slow

Hello,
I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
The same DB on a slower machine running 2000 runs mutch faster.
For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
seconds, 20 times slower.
I reindexed and that did not help.
Any Ideas?
Why the same DB with same indexes behaving that way, what Am I missing'
This is the release version of sql2005, Stardard edition, RTM...
Thanks
SAAre you accessing the database from a .Net 1.1 application using the standar
d
SqlConnection / related classes? I found that my .NET 1.1 apps would not
connect to a SQL Server 2005 instance using a shared memory connection.
Recompiling the same code with the .NET 2.0 framework resolved the issue -
the application again connected using shared memory and was sigificantly
faster. I don't know how to go about determining what mode (tcp / names
pipes / shared memory) a given connection is using, but I'm sure a quick
search will answer that.
Ross
"MSDN" wrote:

> Hello,
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I missing'
'
> This is the release version of sql2005, Stardard edition, RTM...
>
> Thanks
> SA
>
>|||Have you run UPDATE TATISTICS with FULLSCAN option?
"MSDN" <sql_agentman@.hotmail.com> wrote in message
news:OD02m01NGHA.2668@.tk2msftngp13.phx.gbl...
> Hello,
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I
> missing'
> This is the release version of sql2005, Stardard edition, RTM...
>
> Thanks
> SA
>|||Sorry
UPDATE STATISTICS
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OMQ9gf3NGHA.3864@.TK2MSFTNGP10.phx.gbl...
> Have you run UPDATE TATISTICS with FULLSCAN option?
>
>
> "MSDN" <sql_agentman@.hotmail.com> wrote in message
> news:OD02m01NGHA.2668@.tk2msftngp13.phx.gbl...
>|||MSDN (sql_agentman@.hotmail.com) writes:
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I
> missing'
As Uri pointed you must run UPDATE STATISTICS WITH FULLSCAN on all your
tables. The statistics from SQL 2000 are invalidated when you upgrade.
There may be more to it than that, but start there.
If you need further assistence, please be more specific of what is slow.
Is it certain queries, or is it slower overall? If you run queries from
Query Analyzer, is there still any differences (to rule out connection
issues as suggested in Ross's post). If you run from the local machine
(to exclude network issues)?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Perhaps it's not the database, but configuration of the SQL Server
installation. Also, if this database is running on a new server box, it may
be related hardware or OS configuration.
The following was copied from the MSDN article titled: Checklist: SQL Server
Performance
Use default server configuration settings for most applications.
Locate logs and the tempdb database on separate devices from the data.
Provide separate devices for heavily accessed tables and indexes.
Use the correct RAID configuration.
Use multiple disk controllers.
Pre-grow databases and logs to avoid automatic growth and fragmentation
performance impact.
Maximize available memory.
Manage index fragmentation.
Keep database administrator tasks in mind.
http://msdn.microsoft.com/SQL/2000/...enetcheck08.asp
"MSDN" <sql_agentman@.hotmail.com> wrote in message
news:OD02m01NGHA.2668@.tk2msftngp13.phx.gbl...
> Hello,
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I
> missing'
> This is the release version of sql2005, Stardard edition, RTM...
>
> Thanks
> SA
>sql

moved from ms sql 2000 to ms sql2005

database has been recently upgraded from ms sql 2000 to ms sql 2005.
are there anything I need to be aware after upgrading to ms sql 2005?

for my experience, i got an error if i use column alias in ORDER BY
clause which was fine on ms sql 200.

thanksHandersonVA (handersonva@.hotmail.com) writes:

Quote:

Originally Posted by

database has been recently upgraded from ms sql 2000 to ms sql 2005.
are there anything I need to be aware after upgrading to ms sql 2005?

Run sp_updatestats on all databases, since statistics from SQL 2000 are
invalidated with the upgrade.

Quote:

Originally Posted by

for my experience, i got an error if i use column alias in ORDER BY
clause which was fine on ms sql 200.

It's fine in SQL 2005 too. However, there were bugs in SQL 2000 which lead
to incorrect code being accepted. For instance in SQL 2000 you can
say:

SELECT name FROM sysobjects ORDER BY myownalias.name

This is correctly rejected in SQL 2005. There are a couple of variations
on this theme.

Another issue that has bitten more that one is that they had views
like:

CREATE VIEW myview AS
SELECT TOP 100 PERCENT ...
ORDER BY somecol

then they expect "SELECT ... FORM myview" to always return data ordered
by somecol. SQL 2000 usually honors that, which is mere chance. On
SQL 2005 you are less lucky. The moral is that you should always
specify an ORDER BY clause on SELECT statements that produces data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

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

Move SQL2005 from Default Instance to Named Instance

I have a server with sql server 2005 installed as the default instance -- I have a piece of software that needs SQL2000 to be the default instance. Is there a way other than install new sql2005 named instance and move databases to rename my SQL2005 instance from <machinename> to <machinename>\sql05 for example?

Bryan

SQL Server 2005 instance name cannot be changed after installation. You need to install second names instance side by side, old the databases and uninstall the default instance. Then you can install SQL 2000 default instance.

|||Not unexpected and not unreasonable that this cannot be done. Just hoping I could be a little lazier

Move SQL2005 from Default Instance to Named Instance

I have a server with sql server 2005 installed as the default instance -- I have a piece of software that needs SQL2000 to be the default instance. Is there a way other than install new sql2005 named instance and move databases to rename my SQL2005 instance from <machinename> to <machinename>\sql05 for example?

Bryan

SQL Server 2005 instance name cannot be changed after installation. You need to install second names instance side by side, old the databases and uninstall the default instance. Then you can install SQL 2000 default instance.

|||Not unexpected and not unreasonable that this cannot be done. Just hoping I could be a little lazier

move sql 2000 db to sql 2005

Hi, All,
I want to move sql 2000 db to sql 2005, including data and user. The sql
2005 is on the remote server rather than local one. I knew using
import/export to move data, but don't know on how to move user info. Also
using copy database can copy 2000 to 2005, but before copy, need no
application and service to access 2000 db, so 1) how to make no application
and service access database? 2) anyone can tell detail procedure on how to
move?
Thanks in advance for your time,
Martin
Thank you.
as for sp_help_revlogin procedure from KB, what is KB?
"Tibor Karaszi" wrote:

> I suggest you use BACKUP and RESTORE to move the database. It is fully online. Also, get the
> sp_help_revlogin procedure from KB and use that to move over your login (this way the users' SID
> will match the login's SID).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:E297ABCE-4F35-46CC-8BAC-98F803BA17B9@.microsoft.com...
>
|||Thanks again
"Tibor Karaszi" wrote:

> KB = Microsoft KnowledgeBase:
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;kbhowto&sd=GN&=EN-US&FR=0
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:4CDECE99-FCEC-45AA-9090-B5CA8385C70C@.microsoft.com...
>
|||Hi, All,
Import/Export, Copy Database Wizard, and Backup/Restore can copy sql 2000
data to sql 2005, can anyone know which is better way to upgrade sql 2000 to
2005?
Thanks,
Martin
"Tibor Karaszi" wrote:

> KB = Microsoft KnowledgeBase:
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;kbhowto&sd=GN&=EN-US&FR=0
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:4CDECE99-FCEC-45AA-9090-B5CA8385C70C@.microsoft.com...
>
|||Thank you all for this help!
Martin
"Tibor Karaszi" wrote:

> I believe that there has been some bugs in Copy Database Wizard. I've only read about them, didn't
> memorize them, but it was enough for me to avoid it. I'm sure MS has been working on for sp2,
> though.
> I generally prefer working on the binary level (backup/restore or detach/attach) instead of
> Export/Import. When you use the later, the database is scripted (a bunch of CREATE statements), the
> script files are executed against the destination, and then the data is transferred. If you aren't
> prepared to handle any possible errors in the script handling (if it isn't "your" data model"), then
> I recommend against Export/Import.
> That leaves us with detach/attach (which is what Copy Database Wizard is using) and BACKUP/RESTORE.
> I prefer Backup/Restore as that don't affect the source database, and the amount of data to transfer
> is less (empty space isn't included in backup), I only get one file etc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:F6C87F0D-2BBD-40F8-90C4-9F9A40CFE0D4@.microsoft.com...
>
|||The best thing would be detach and create for attach. I did the same for a
1.5TB database having 4 datafiles on multiple filegroups. It all went well
on SQL2005.
Note: You might have to run dbcc checkdb, dbcc updateusage after the
mgiration.
thks,
Manikanth.S
MCDBA.
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:37F7AE5F-69C0-4C67-9CF7-23320A3911F1@.microsoft.com...[vbcol=seagreen]
> Thank you all for this help!
> Martin
> "Tibor Karaszi" wrote:

move sql 2000 db to sql 2005

Hi, All,
I want to move sql 2000 db to sql 2005, including data and user. The sql
2005 is on the remote server rather than local one. I knew using
import/export to move data, but don't know on how to move user info. Also
using copy database can copy 2000 to 2005, but before copy, need no
application and service to access 2000 db, so 1) how to make no application
and service access database? 2) anyone can tell detail procedure on how to
move?
Thanks in advance for your time,
MartinI suggest you use BACKUP and RESTORE to move the database. It is fully onlin
e. Also, get the
sp_help_revlogin procedure from KB and use that to move over your login (thi
s way the users' SID
will match the login's SID).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:E297ABCE-4F35-46CC-8BAC-98F803BA17B9@.microsoft.com...
> Hi, All,
> I want to move sql 2000 db to sql 2005, including data and user. The sql
> 2005 is on the remote server rather than local one. I knew using
> import/export to move data, but don't know on how to move user info. Also
> using copy database can copy 2000 to 2005, but before copy, need no
> application and service to access 2000 db, so 1) how to make no applicati
on
> and service access database? 2) anyone can tell detail procedure on how t
o
> move?
> Thanks in advance for your time,
> Martin|||Thank you.
as for sp_help_revlogin procedure from KB, what is KB?
"Tibor Karaszi" wrote:

> I suggest you use BACKUP and RESTORE to move the database. It is fully onl
ine. Also, get the
> sp_help_revlogin procedure from KB and use that to move over your login (t
his way the users' SID
> will match the login's SID).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:E297ABCE-4F35-46CC-8BAC-98F803BA17B9@.microsoft.com...
>|||KB = Microsoft KnowledgeBase:
http://support.microsoft.com/defaul...ver/default.asp
http://www.solidqualitylearning.com/
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:4CDECE99-FCEC-45AA-9090-B5CA8385C70C@.microsoft.com...[vbcol=seagreen]
> Thank you.
> as for sp_help_revlogin procedure from KB, what is KB?
> "Tibor Karaszi" wrote:
>|||Thanks again
"Tibor Karaszi" wrote:

> KB = Microsoft KnowledgeBase:
> http://support.microsoft.com/defaul...US&FR=
0
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:4CDECE99-FCEC-45AA-9090-B5CA8385C70C@.microsoft.com...
>|||Hi, All,
Import/Export, Copy Database Wizard, and Backup/Restore can copy sql 2000
data to sql 2005, can anyone know which is better way to upgrade sql 2000 to
2005?
Thanks,
Martin
"Tibor Karaszi" wrote:

> KB = Microsoft KnowledgeBase:
> http://support.microsoft.com/defaul...US&FR=
0
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:4CDECE99-FCEC-45AA-9090-B5CA8385C70C@.microsoft.com...
>|||I believe that there has been some bugs in Copy Database Wizard. I've only r
ead about them, didn't
memorize them, but it was enough for me to avoid it. I'm sure MS has been wo
rking on for sp2,
though.
I generally prefer working on the binary level (backup/restore or detach/att
ach) instead of
Export/Import. When you use the later, the database is scripted (a bunch of
CREATE statements), the
script files are executed against the destination, and then the data is tran
sferred. If you aren't
prepared to handle any possible errors in the script handling (if it isn't "
your" data model"), then
I recommend against Export/Import.
That leaves us with detach/attach (which is what Copy Database Wizard is usi
ng) and BACKUP/RESTORE.
I prefer Backup/Restore as that don't affect the source database, and the am
ount of data to transfer
is less (empty space isn't included in backup), I only get one file etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:F6C87F0D-2BBD-40F8-90C4-9F9A40CFE0D4@.microsoft.com...[vbcol=seagreen]
> Hi, All,
> Import/Export, Copy Database Wizard, and Backup/Restore can copy sql 2000
> data to sql 2005, can anyone know which is better way to upgrade sql 2000
to
> 2005?
> Thanks,
> Martin
> "Tibor Karaszi" wrote:
>|||Backup / restore is best, as it copies everything and gives you the ability
to save the intermediate state. The Copy Database Wizard is the easiest to
use, it also copies everything. Import/Export discards a lot of imformation
and is therefore not an option for copying whole databases.
If you are replacing SQL2000 with SQL2005, you can also just detach and
attach the files.
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:F6C87F0D-2BBD-40F8-90C4-9F9A40CFE0D4@.microsoft.com...[vbcol=seagreen]
> Hi, All,
> Import/Export, Copy Database Wizard, and Backup/Restore can copy sql 2000
> data to sql 2005, can anyone know which is better way to upgrade sql 2000
> to
> 2005?
> Thanks,
> Martin
> "Tibor Karaszi" wrote:
>|||Thank you all for this help!
Martin
"Tibor Karaszi" wrote:

> I believe that there has been some bugs in Copy Database Wizard. I've only
read about them, didn't
> memorize them, but it was enough for me to avoid it. I'm sure MS has been
working on for sp2,
> though.
> I generally prefer working on the binary level (backup/restore or detach/a
ttach) instead of
> Export/Import. When you use the later, the database is scripted (a bunch o
f CREATE statements), the
> script files are executed against the destination, and then the data is tr
ansferred. If you aren't
> prepared to handle any possible errors in the script handling (if it isn't
"your" data model"), then
> I recommend against Export/Import.
> That leaves us with detach/attach (which is what Copy Database Wizard is u
sing) and BACKUP/RESTORE.
> I prefer Backup/Restore as that don't affect the source database, and the
amount of data to transfer
> is less (empty space isn't included in backup), I only get one file etc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:F6C87F0D-2BBD-40F8-90C4-9F9A40CFE0D4@.microsoft.com...
>|||The best thing would be detach and create for attach. I did the same for a
1.5TB database having 4 datafiles on multiple filegroups. It all went well
on SQL2005.
Note: You might have to run dbcc checkdb, dbcc updateusage after the
mgiration.
thks,
Manikanth.S
MCDBA.
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:37F7AE5F-69C0-4C67-9CF7-23320A3911F1@.microsoft.com...[vbcol=seagreen]
> Thank you all for this help!
> Martin
> "Tibor Karaszi" wrote:
>