Hi -
I've looked on Microsoft's web site:
http://support.microsoft.com/default...;EN-US;Q240867
and not yet been able to find a full answer to my question.
I'd appreciate any insight from others:
I have a database that has full text indexes on it, that I need to log ship
to multiple other (destination) servers. Also, to promote availability on
each of the destination servers, I plan to restore into two databases:
dest_db and dest_db_alt. Both are to have the same content, but different db
names. I intend to restore one set of dbs/logs from the source server into
standby mode in each destination database, sequentially, so that, with
proper client re-direction, a client hitting the destination server will
always get redirected to a read-only db in standby mode. While one db is
restoring, the other is readable. Ok, fair enough.
Now, the tricky part. The source db is freextex indexed, and one can't
build/rebuild freetext indexes on a db in standby or read-only mode. So,
apparently I'll have to copy the freetext index directory trees from the
source server and restore them on the destination server. What I'm uncertain
about is whether I'll be able to:
a) Use one set of freetext indexes files for both destination dbs (I don't
think so, If I understand things correctly),
b) Properly set the registry keys and directory/file names on the freetext
catalogs on the destination server so that even though at least one of the
destination dbs (necessarily) has a different dbid than the source database,
it will still be able to have a functioning freetext index. From the
generally related material I read it seems as if the registry keys map the
freetext catalog path(s) into SQL Server, and those catalog paths use dbids
and ftcatids integrated into their folder names to map a set of catalog
files to a dbid.
So here is my question: Can I just make multiple copies of the freetext
catalog files, differing only in the dbid portion of the folder name, and
then, using registry keys, map the newly created Freetext catalog folder(s)
into SQL Server with the relevant dbids on the destination server ?
Are dbids, or other database specific unique identifiers, hard coded within
the freetext catalog files themselves ?
Is there other important information I have to consider here ?
Thanks
Steve
Use replication for this. Create your catalogs and full text indexes using a
post snapshot script or create the tables and catalogs in advance, full text
index the tables, and then configure your article to delete the data not
drop and recreate the table (in the article properties section select the
browse button to the right of your table name, and in the snapshot tab, in
the name conflicts section , select delete all data).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Steve" <Steve_a013452@.newsgroups.nospam> wrote in message
news:OJ9F4IqGGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Hi -
> I've looked on Microsoft's web site:
> http://support.microsoft.com/default...;EN-US;Q240867
> and not yet been able to find a full answer to my question.
> I'd appreciate any insight from others:
> I have a database that has full text indexes on it, that I need to log
> ship to multiple other (destination) servers. Also, to promote
> availability on each of the destination servers, I plan to restore into
> two databases: dest_db and dest_db_alt. Both are to have the same content,
> but different db names. I intend to restore one set of dbs/logs from the
> source server into standby mode in each destination database,
> sequentially, so that, with proper client re-direction, a client hitting
> the destination server will always get redirected to a read-only db in
> standby mode. While one db is restoring, the other is readable. Ok, fair
> enough.
> Now, the tricky part. The source db is freextex indexed, and one can't
> build/rebuild freetext indexes on a db in standby or read-only mode. So,
> apparently I'll have to copy the freetext index directory trees from the
> source server and restore them on the destination server. What I'm
> uncertain about is whether I'll be able to:
> a) Use one set of freetext indexes files for both destination dbs (I don't
> think so, If I understand things correctly),
> b) Properly set the registry keys and directory/file names on the freetext
> catalogs on the destination server so that even though at least one of the
> destination dbs (necessarily) has a different dbid than the source
> database, it will still be able to have a functioning freetext index. From
> the generally related material I read it seems as if the registry keys map
> the freetext catalog path(s) into SQL Server, and those catalog paths use
> dbids and ftcatids integrated into their folder names to map a set of
> catalog files to a dbid.
> So here is my question: Can I just make multiple copies of the freetext
> catalog files, differing only in the dbid portion of the folder name, and
> then, using registry keys, map the newly created Freetext catalog
> folder(s) into SQL Server with the relevant dbids on the destination
> server ?
> Are dbids, or other database specific unique identifiers, hard coded
> within the freetext catalog files themselves ?
> Is there other important information I have to consider here ?
> Thanks
> Steve
>
Showing posts with label destination. Show all posts
Showing posts with label destination. Show all posts
Monday, March 19, 2012
Thursday, March 8, 2012
copying data from 1 table to another
just wondering the best way to copy data from one database to another after
doing a dbase port? The destination database works with a .NET application
and the source database came from a My SQL database (used a mysql driver and
access to get the data to SQL2000 dbase). Also the source database does not
have constraints setup but the the destination tables do, so just want to
fill the destination tables with the data from the source tables. I have the
Query Analizer tool so was thinking there may be an easy way to copy the data
using this tool.
Thanks.
Paul G
Software engineer.
It depends with which tool you are the most confortable. Quick way could be
to use:
-insert into <destinationTable> select * from <SourceTable>. Make sure you
try a on a test database first.
You could also use the bulk copy utility. It depends how much data and how
many tables you have. Note that the first solution will increase your
transaction log size if you have a lot of data.
There is also DTS.
"Paul" wrote:
> just wondering the best way to copy data from one database to another after
> doing a dbase port? The destination database works with a .NET application
> and the source database came from a My SQL database (used a mysql driver and
> access to get the data to SQL2000 dbase). Also the source database does not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have the
> Query Analizer tool so was thinking there may be an easy way to copy the data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.
|||Hi
If the source database is outside SQL Server you may want to look at DTS or
the Import Wizard in Enterprise Manager (assuming that you don't have
MSDE!). If you save this as a package then it will be modifyable and
repeatable. If they are in the same database you can use INSERT...SELECT
statements (See Books online) to copy from one table to another. These could
be put into a script or DTS package so that you can run them when needed.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> just wondering the best way to copy data from one database to another
after
> doing a dbase port? The destination database works with a .NET
application
> and the source database came from a My SQL database (used a mysql driver
and
> access to get the data to SQL2000 dbase). Also the source database does
not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have
the
> Query Analizer tool so was thinking there may be an easy way to copy the
data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.
|||Thanks for the information. The largest table has around 16k records of
about 10 fields, one other table has around 8k records and the remaining 8
tables are very small. Thinking of just using the query analyser with insert
into <database1.table1>
select * from <database2.table1> since I am somewhat comfortable with the
tool.
I am assuming you can copy from 1 dbase to another.
"Sasan Saidi" wrote:
[vbcol=seagreen]
> It depends with which tool you are the most confortable. Quick way could be
> to use:
> -insert into <destinationTable> select * from <SourceTable>. Make sure you
> try a on a test database first.
> You could also use the bulk copy utility. It depends how much data and how
> many tables you have. Note that the first solution will increase your
> transaction log size if you have a lot of data.
> There is also DTS.
>
> "Paul" wrote:
|||Thanks for the response. The source database was MySQL but I now have it on
SQL2000 server, same server as the destination database. Will probably try
to use the insert, select statements, hopefully can use them copying between
databases. A DTS package seems to be quite useful but have not created any
before but will look into this in the future.
"John Bell" wrote:
> Hi
> If the source database is outside SQL Server you may want to look at DTS or
> the Import Wizard in Enterprise Manager (assuming that you don't have
> MSDE!). If you save this as a package then it will be modifyable and
> repeatable. If they are in the same database you can use INSERT...SELECT
> statements (See Books online) to copy from one table to another. These could
> be put into a script or DTS package so that you can run them when needed.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> after
> application
> and
> not
> the
> data
>
>
|||Hi
There are several ways to copy a compelete database from one location to
another. The easiest if probably to backup/restore. If you backup/restore to
another database on the same computer then you will need to rename data and
log files (similarly if they are in a different location on the destination
computer you will have to do the same).
http://support.microsoft.com/default...n-us;Q314546#2
If you are doing this from Query Analyser check out the WITH MOVE syntax of
the RESTORE command in Books online.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> Thanks for the information. The largest table has around 16k records of
> about 10 fields, one other table has around 8k records and the remaining 8
> tables are very small. Thinking of just using the query analyser with
insert[vbcol=seagreen]
> into <database1.table1>
> select * from <database2.table1> since I am somewhat comfortable with the
> tool.
> I am assuming you can copy from 1 dbase to another.
> "Sasan Saidi" wrote:
be[vbcol=seagreen]
you[vbcol=seagreen]
how[vbcol=seagreen]
after[vbcol=seagreen]
application[vbcol=seagreen]
driver and[vbcol=seagreen]
does not[vbcol=seagreen]
to[vbcol=seagreen]
have the[vbcol=seagreen]
the data[vbcol=seagreen]
|||Hi thanks for the additional information. Since I have made some changes to
a few of the tables, added additional fields and added table constraints like
(no duplicate records allowed) probably should not do a complete dbase copy
but good information to know.
"John Bell" wrote:
> Hi
> There are several ways to copy a compelete database from one location to
> another. The easiest if probably to backup/restore. If you backup/restore to
> another database on the same computer then you will need to rename data and
> log files (similarly if they are in a different location on the destination
> computer you will have to do the same).
> http://support.microsoft.com/default...n-us;Q314546#2
> If you are doing this from Query Analyser check out the WITH MOVE syntax of
> the RESTORE command in Books online.
> John
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> insert
> be
> you
> how
> after
> application
> driver and
> does not
> to
> have the
> the data
>
>
|||Hi Paul
Now you are talking about dbase copy! I was talking about copying SQL Server
databases!!!
In some cirumstances when alot of manipulation has to be done it is often
easier to load everything into staging tables and then do whatever work is
needed using them. A single DTS package may be created to do all of this is
you wanted a reproducable process, this which is often the case as you may
to several trial runs before you have the data in the form you want. It
could also reduce the downtime when you do implement the final data
migration.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> Hi thanks for the additional information. Since I have made some changes
to
> a few of the tables, added additional fields and added table constraints
like
> (no duplicate records allowed) probably should not do a complete dbase
copy[vbcol=seagreen]
> but good information to know.
> "John Bell" wrote:
backup/restore to[vbcol=seagreen]
and[vbcol=seagreen]
destination[vbcol=seagreen]
of[vbcol=seagreen]
of[vbcol=seagreen]
remaining 8[vbcol=seagreen]
the[vbcol=seagreen]
could[vbcol=seagreen]
sure[vbcol=seagreen]
and[vbcol=seagreen]
your[vbcol=seagreen]
another[vbcol=seagreen]
database[vbcol=seagreen]
want[vbcol=seagreen]
I[vbcol=seagreen]
copy[vbcol=seagreen]
|||Thanks for he additional information. Staging or temporary tables seems very
useful. Not familiar with DTS packages imagine there is a bit of a learning
curve, will look under Query Analyser help. The process will only have to be
repeated one time but still be nice to have it in a script or package.
"John Bell" wrote:
> Hi Paul
> Now you are talking about dbase copy! I was talking about copying SQL Server
> databases!!!
> In some cirumstances when alot of manipulation has to be done it is often
> easier to load everything into staging tables and then do whatever work is
> needed using them. A single DTS package may be created to do all of this is
> you wanted a reproducable process, this which is often the case as you may
> to several trial runs before you have the data in the form you want. It
> could also reduce the downtime when you do implement the final data
> migration.
>
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> to
> like
> copy
> backup/restore to
> and
> destination
> of
> of
> remaining 8
> the
> could
> sure
> and
> your
> another
> database
> want
> I
> copy
>
>
doing a dbase port? The destination database works with a .NET application
and the source database came from a My SQL database (used a mysql driver and
access to get the data to SQL2000 dbase). Also the source database does not
have constraints setup but the the destination tables do, so just want to
fill the destination tables with the data from the source tables. I have the
Query Analizer tool so was thinking there may be an easy way to copy the data
using this tool.
Thanks.
Paul G
Software engineer.
It depends with which tool you are the most confortable. Quick way could be
to use:
-insert into <destinationTable> select * from <SourceTable>. Make sure you
try a on a test database first.
You could also use the bulk copy utility. It depends how much data and how
many tables you have. Note that the first solution will increase your
transaction log size if you have a lot of data.
There is also DTS.
"Paul" wrote:
> just wondering the best way to copy data from one database to another after
> doing a dbase port? The destination database works with a .NET application
> and the source database came from a My SQL database (used a mysql driver and
> access to get the data to SQL2000 dbase). Also the source database does not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have the
> Query Analizer tool so was thinking there may be an easy way to copy the data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.
|||Hi
If the source database is outside SQL Server you may want to look at DTS or
the Import Wizard in Enterprise Manager (assuming that you don't have
MSDE!). If you save this as a package then it will be modifyable and
repeatable. If they are in the same database you can use INSERT...SELECT
statements (See Books online) to copy from one table to another. These could
be put into a script or DTS package so that you can run them when needed.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> just wondering the best way to copy data from one database to another
after
> doing a dbase port? The destination database works with a .NET
application
> and the source database came from a My SQL database (used a mysql driver
and
> access to get the data to SQL2000 dbase). Also the source database does
not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have
the
> Query Analizer tool so was thinking there may be an easy way to copy the
data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.
|||Thanks for the information. The largest table has around 16k records of
about 10 fields, one other table has around 8k records and the remaining 8
tables are very small. Thinking of just using the query analyser with insert
into <database1.table1>
select * from <database2.table1> since I am somewhat comfortable with the
tool.
I am assuming you can copy from 1 dbase to another.
"Sasan Saidi" wrote:
[vbcol=seagreen]
> It depends with which tool you are the most confortable. Quick way could be
> to use:
> -insert into <destinationTable> select * from <SourceTable>. Make sure you
> try a on a test database first.
> You could also use the bulk copy utility. It depends how much data and how
> many tables you have. Note that the first solution will increase your
> transaction log size if you have a lot of data.
> There is also DTS.
>
> "Paul" wrote:
|||Thanks for the response. The source database was MySQL but I now have it on
SQL2000 server, same server as the destination database. Will probably try
to use the insert, select statements, hopefully can use them copying between
databases. A DTS package seems to be quite useful but have not created any
before but will look into this in the future.
"John Bell" wrote:
> Hi
> If the source database is outside SQL Server you may want to look at DTS or
> the Import Wizard in Enterprise Manager (assuming that you don't have
> MSDE!). If you save this as a package then it will be modifyable and
> repeatable. If they are in the same database you can use INSERT...SELECT
> statements (See Books online) to copy from one table to another. These could
> be put into a script or DTS package so that you can run them when needed.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> after
> application
> and
> not
> the
> data
>
>
|||Hi
There are several ways to copy a compelete database from one location to
another. The easiest if probably to backup/restore. If you backup/restore to
another database on the same computer then you will need to rename data and
log files (similarly if they are in a different location on the destination
computer you will have to do the same).
http://support.microsoft.com/default...n-us;Q314546#2
If you are doing this from Query Analyser check out the WITH MOVE syntax of
the RESTORE command in Books online.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> Thanks for the information. The largest table has around 16k records of
> about 10 fields, one other table has around 8k records and the remaining 8
> tables are very small. Thinking of just using the query analyser with
insert[vbcol=seagreen]
> into <database1.table1>
> select * from <database2.table1> since I am somewhat comfortable with the
> tool.
> I am assuming you can copy from 1 dbase to another.
> "Sasan Saidi" wrote:
be[vbcol=seagreen]
you[vbcol=seagreen]
how[vbcol=seagreen]
after[vbcol=seagreen]
application[vbcol=seagreen]
driver and[vbcol=seagreen]
does not[vbcol=seagreen]
to[vbcol=seagreen]
have the[vbcol=seagreen]
the data[vbcol=seagreen]
|||Hi thanks for the additional information. Since I have made some changes to
a few of the tables, added additional fields and added table constraints like
(no duplicate records allowed) probably should not do a complete dbase copy
but good information to know.
"John Bell" wrote:
> Hi
> There are several ways to copy a compelete database from one location to
> another. The easiest if probably to backup/restore. If you backup/restore to
> another database on the same computer then you will need to rename data and
> log files (similarly if they are in a different location on the destination
> computer you will have to do the same).
> http://support.microsoft.com/default...n-us;Q314546#2
> If you are doing this from Query Analyser check out the WITH MOVE syntax of
> the RESTORE command in Books online.
> John
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> insert
> be
> you
> how
> after
> application
> driver and
> does not
> to
> have the
> the data
>
>
|||Hi Paul
Now you are talking about dbase copy! I was talking about copying SQL Server
databases!!!
In some cirumstances when alot of manipulation has to be done it is often
easier to load everything into staging tables and then do whatever work is
needed using them. A single DTS package may be created to do all of this is
you wanted a reproducable process, this which is often the case as you may
to several trial runs before you have the data in the form you want. It
could also reduce the downtime when you do implement the final data
migration.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> Hi thanks for the additional information. Since I have made some changes
to
> a few of the tables, added additional fields and added table constraints
like
> (no duplicate records allowed) probably should not do a complete dbase
copy[vbcol=seagreen]
> but good information to know.
> "John Bell" wrote:
backup/restore to[vbcol=seagreen]
and[vbcol=seagreen]
destination[vbcol=seagreen]
of[vbcol=seagreen]
of[vbcol=seagreen]
remaining 8[vbcol=seagreen]
the[vbcol=seagreen]
could[vbcol=seagreen]
sure[vbcol=seagreen]
and[vbcol=seagreen]
your[vbcol=seagreen]
another[vbcol=seagreen]
database[vbcol=seagreen]
want[vbcol=seagreen]
I[vbcol=seagreen]
copy[vbcol=seagreen]
|||Thanks for he additional information. Staging or temporary tables seems very
useful. Not familiar with DTS packages imagine there is a bit of a learning
curve, will look under Query Analyser help. The process will only have to be
repeated one time but still be nice to have it in a script or package.
"John Bell" wrote:
> Hi Paul
> Now you are talking about dbase copy! I was talking about copying SQL Server
> databases!!!
> In some cirumstances when alot of manipulation has to be done it is often
> easier to load everything into staging tables and then do whatever work is
> needed using them. A single DTS package may be created to do all of this is
> you wanted a reproducable process, this which is often the case as you may
> to several trial runs before you have the data in the form you want. It
> could also reduce the downtime when you do implement the final data
> migration.
>
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> to
> like
> copy
> backup/restore to
> and
> destination
> of
> of
> remaining 8
> the
> could
> sure
> and
> your
> another
> database
> want
> I
> copy
>
>
copying data from 1 table to another
just wondering the best way to copy data from one database to another after
doing a dbase port? The destination database works with a .NET application
and the source database came from a My SQL database (used a mysql driver and
access to get the data to SQL2000 dbase). Also the source database does not
have constraints setup but the the destination tables do, so just want to
fill the destination tables with the data from the source tables. I have the
Query Analizer tool so was thinking there may be an easy way to copy the dat
a
using this tool.
Thanks.
--
Paul G
Software engineer.It depends with which tool you are the most confortable. Quick way could be
to use:
-insert into <destinationTable> select * from <SourceTable>. Make sure you
try a on a test database first.
You could also use the bulk copy utility. It depends how much data and how
many tables you have. Note that the first solution will increase your
transaction log size if you have a lot of data.
There is also DTS.
"Paul" wrote:
> just wondering the best way to copy data from one database to another afte
r
> doing a dbase port? The destination database works with a .NET applicatio
n
> and the source database came from a My SQL database (used a mysql driver a
nd
> access to get the data to SQL2000 dbase). Also the source database does n
ot
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have t
he
> Query Analizer tool so was thinking there may be an easy way to copy the d
ata
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi
If the source database is outside SQL Server you may want to look at DTS or
the Import Wizard in Enterprise Manager (assuming that you don't have
MSDE!). If you save this as a package then it will be modifyable and
repeatable. If they are in the same database you can use INSERT...SELECT
statements (See Books online) to copy from one table to another. These could
be put into a script or DTS package so that you can run them when needed.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> just wondering the best way to copy data from one database to another
after
> doing a dbase port? The destination database works with a .NET
application
> and the source database came from a My SQL database (used a mysql driver
and
> access to get the data to SQL2000 dbase). Also the source database does
not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have
the
> Query Analizer tool so was thinking there may be an easy way to copy the
data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.|||Thanks for the information. The largest table has around 16k records of
about 10 fields, one other table has around 8k records and the remaining 8
tables are very small. Thinking of just using the query analyser with inser
t
into <database1.table1>
select * from <database2.table1> since I am somewhat comfortable with the
tool.
I am assuming you can copy from 1 dbase to another.
"Sasan Saidi" wrote:
[vbcol=seagreen]
> It depends with which tool you are the most confortable. Quick way could b
e
> to use:
> -insert into <destinationTable> select * from <SourceTable>. Make sure yo
u
> try a on a test database first.
> You could also use the bulk copy utility. It depends how much data and how
> many tables you have. Note that the first solution will increase your
> transaction log size if you have a lot of data.
> There is also DTS.
>
> "Paul" wrote:
>|||Thanks for the response. The source database was mysql but I now have it on
SQL2000 server, same server as the destination database. Will probably try
to use the insert, select statements, hopefully can use them copying between
databases. A DTS package seems to be quite useful but have not created any
before but will look into this in the future.
"John Bell" wrote:
> Hi
> If the source database is outside SQL Server you may want to look at DTS o
r
> the Import Wizard in Enterprise Manager (assuming that you don't have
> MSDE!). If you save this as a package then it will be modifyable and
> repeatable. If they are in the same database you can use INSERT...SELECT
> statements (See Books online) to copy from one table to another. These cou
ld
> be put into a script or DTS package so that you can run them when needed.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> after
> application
> and
> not
> the
> data
>
>|||Hi
There are several ways to copy a compelete database from one location to
another. The easiest if probably to backup/restore. If you backup/restore to
another database on the same computer then you will need to rename data and
log files (similarly if they are in a different location on the destination
computer you will have to do the same).
http://support.microsoft.com/defaul...en-us;Q314546#2
If you are doing this from Query Analyser check out the WITH MOVE syntax of
the RESTORE command in Books online.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> Thanks for the information. The largest table has around 16k records of
> about 10 fields, one other table has around 8k records and the remaining 8
> tables are very small. Thinking of just using the query analyser with
insert[vbcol=seagreen]
> into <database1.table1>
> select * from <database2.table1> since I am somewhat comfortable with the
> tool.
> I am assuming you can copy from 1 dbase to another.
> "Sasan Saidi" wrote:
>
be[vbcol=seagreen]
you[vbcol=seagreen]
how[vbcol=seagreen]
after[vbcol=seagreen]
application[vbcol=seagreen]
driver and[vbcol=seagreen]
does not[vbcol=seagreen]
to[vbcol=seagreen]
have the[vbcol=seagreen]
the data[vbcol=seagreen]|||Hi thanks for the additional information. Since I have made some changes to
a few of the tables, added additional fields and added table constraints lik
e
(no duplicate records allowed) probably should not do a complete dbase copy
but good information to know.
"John Bell" wrote:
> Hi
> There are several ways to copy a compelete database from one location to
> another. The easiest if probably to backup/restore. If you backup/restore
to
> another database on the same computer then you will need to rename data an
d
> log files (similarly if they are in a different location on the destinatio
n
> computer you will have to do the same).
> http://support.microsoft.com/defaul...en-us;Q314546#2
> If you are doing this from Query Analyser check out the WITH MOVE syntax o
f
> the RESTORE command in Books online.
> John
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> insert
> be
> you
> how
> after
> application
> driver and
> does not
> to
> have the
> the data
>
>|||Hi Paul
Now you are talking about dbase copy! I was talking about copying SQL Server
databases!!!
In some cirumstances when alot of manipulation has to be done it is often
easier to load everything into staging tables and then do whatever work is
needed using them. A single DTS package may be created to do all of this is
you wanted a reproducable process, this which is often the case as you may
to several trial runs before you have the data in the form you want. It
could also reduce the downtime when you do implement the final data
migration.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> Hi thanks for the additional information. Since I have made some changes
to
> a few of the tables, added additional fields and added table constraints
like
> (no duplicate records allowed) probably should not do a complete dbase
copy[vbcol=seagreen]
> but good information to know.
> "John Bell" wrote:
>
backup/restore to[vbcol=seagreen]
and[vbcol=seagreen]
destination[vbcol=seagreen]
of[vbcol=seagreen]
of[vbcol=seagreen]
remaining 8[vbcol=seagreen]
the[vbcol=seagreen]
could[vbcol=seagreen]
sure[vbcol=seagreen]
and[vbcol=seagreen]
your[vbcol=seagreen]
another[vbcol=seagreen]
database[vbcol=seagreen]
want[vbcol=seagreen]
I[vbcol=seagreen]
copy[vbcol=seagreen]|||Thanks for he additional information. Staging or temporary tables seems ver
y
useful. Not familiar with DTS packages imagine there is a bit of a learning
curve, will look under Query Analyser help. The process will only have to b
e
repeated one time but still be nice to have it in a script or package.
"John Bell" wrote:
> Hi Paul
> Now you are talking about dbase copy! I was talking about copying SQL Serv
er
> databases!!!
> In some cirumstances when alot of manipulation has to be done it is often
> easier to load everything into staging tables and then do whatever work is
> needed using them. A single DTS package may be created to do all of this i
s
> you wanted a reproducable process, this which is often the case as you may
> to several trial runs before you have the data in the form you want. It
> could also reduce the downtime when you do implement the final data
> migration.
>
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> to
> like
> copy
> backup/restore to
> and
> destination
> of
> of
> remaining 8
> the
> could
> sure
> and
> your
> another
> database
> want
> I
> copy
>
>
doing a dbase port? The destination database works with a .NET application
and the source database came from a My SQL database (used a mysql driver and
access to get the data to SQL2000 dbase). Also the source database does not
have constraints setup but the the destination tables do, so just want to
fill the destination tables with the data from the source tables. I have the
Query Analizer tool so was thinking there may be an easy way to copy the dat
a
using this tool.
Thanks.
--
Paul G
Software engineer.It depends with which tool you are the most confortable. Quick way could be
to use:
-insert into <destinationTable> select * from <SourceTable>. Make sure you
try a on a test database first.
You could also use the bulk copy utility. It depends how much data and how
many tables you have. Note that the first solution will increase your
transaction log size if you have a lot of data.
There is also DTS.
"Paul" wrote:
> just wondering the best way to copy data from one database to another afte
r
> doing a dbase port? The destination database works with a .NET applicatio
n
> and the source database came from a My SQL database (used a mysql driver a
nd
> access to get the data to SQL2000 dbase). Also the source database does n
ot
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have t
he
> Query Analizer tool so was thinking there may be an easy way to copy the d
ata
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi
If the source database is outside SQL Server you may want to look at DTS or
the Import Wizard in Enterprise Manager (assuming that you don't have
MSDE!). If you save this as a package then it will be modifyable and
repeatable. If they are in the same database you can use INSERT...SELECT
statements (See Books online) to copy from one table to another. These could
be put into a script or DTS package so that you can run them when needed.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> just wondering the best way to copy data from one database to another
after
> doing a dbase port? The destination database works with a .NET
application
> and the source database came from a My SQL database (used a mysql driver
and
> access to get the data to SQL2000 dbase). Also the source database does
not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have
the
> Query Analizer tool so was thinking there may be an easy way to copy the
data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.|||Thanks for the information. The largest table has around 16k records of
about 10 fields, one other table has around 8k records and the remaining 8
tables are very small. Thinking of just using the query analyser with inser
t
into <database1.table1>
select * from <database2.table1> since I am somewhat comfortable with the
tool.
I am assuming you can copy from 1 dbase to another.
"Sasan Saidi" wrote:
[vbcol=seagreen]
> It depends with which tool you are the most confortable. Quick way could b
e
> to use:
> -insert into <destinationTable> select * from <SourceTable>. Make sure yo
u
> try a on a test database first.
> You could also use the bulk copy utility. It depends how much data and how
> many tables you have. Note that the first solution will increase your
> transaction log size if you have a lot of data.
> There is also DTS.
>
> "Paul" wrote:
>|||Thanks for the response. The source database was mysql but I now have it on
SQL2000 server, same server as the destination database. Will probably try
to use the insert, select statements, hopefully can use them copying between
databases. A DTS package seems to be quite useful but have not created any
before but will look into this in the future.
"John Bell" wrote:
> Hi
> If the source database is outside SQL Server you may want to look at DTS o
r
> the Import Wizard in Enterprise Manager (assuming that you don't have
> MSDE!). If you save this as a package then it will be modifyable and
> repeatable. If they are in the same database you can use INSERT...SELECT
> statements (See Books online) to copy from one table to another. These cou
ld
> be put into a script or DTS package so that you can run them when needed.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> after
> application
> and
> not
> the
> data
>
>|||Hi
There are several ways to copy a compelete database from one location to
another. The easiest if probably to backup/restore. If you backup/restore to
another database on the same computer then you will need to rename data and
log files (similarly if they are in a different location on the destination
computer you will have to do the same).
http://support.microsoft.com/defaul...en-us;Q314546#2
If you are doing this from Query Analyser check out the WITH MOVE syntax of
the RESTORE command in Books online.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> Thanks for the information. The largest table has around 16k records of
> about 10 fields, one other table has around 8k records and the remaining 8
> tables are very small. Thinking of just using the query analyser with
insert[vbcol=seagreen]
> into <database1.table1>
> select * from <database2.table1> since I am somewhat comfortable with the
> tool.
> I am assuming you can copy from 1 dbase to another.
> "Sasan Saidi" wrote:
>
be[vbcol=seagreen]
you[vbcol=seagreen]
how[vbcol=seagreen]
after[vbcol=seagreen]
application[vbcol=seagreen]
driver and[vbcol=seagreen]
does not[vbcol=seagreen]
to[vbcol=seagreen]
have the[vbcol=seagreen]
the data[vbcol=seagreen]|||Hi thanks for the additional information. Since I have made some changes to
a few of the tables, added additional fields and added table constraints lik
e
(no duplicate records allowed) probably should not do a complete dbase copy
but good information to know.
"John Bell" wrote:
> Hi
> There are several ways to copy a compelete database from one location to
> another. The easiest if probably to backup/restore. If you backup/restore
to
> another database on the same computer then you will need to rename data an
d
> log files (similarly if they are in a different location on the destinatio
n
> computer you will have to do the same).
> http://support.microsoft.com/defaul...en-us;Q314546#2
> If you are doing this from Query Analyser check out the WITH MOVE syntax o
f
> the RESTORE command in Books online.
> John
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> insert
> be
> you
> how
> after
> application
> driver and
> does not
> to
> have the
> the data
>
>|||Hi Paul
Now you are talking about dbase copy! I was talking about copying SQL Server
databases!!!
In some cirumstances when alot of manipulation has to be done it is often
easier to load everything into staging tables and then do whatever work is
needed using them. A single DTS package may be created to do all of this is
you wanted a reproducable process, this which is often the case as you may
to several trial runs before you have the data in the form you want. It
could also reduce the downtime when you do implement the final data
migration.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> Hi thanks for the additional information. Since I have made some changes
to
> a few of the tables, added additional fields and added table constraints
like
> (no duplicate records allowed) probably should not do a complete dbase
copy[vbcol=seagreen]
> but good information to know.
> "John Bell" wrote:
>
backup/restore to[vbcol=seagreen]
and[vbcol=seagreen]
destination[vbcol=seagreen]
of[vbcol=seagreen]
of[vbcol=seagreen]
remaining 8[vbcol=seagreen]
the[vbcol=seagreen]
could[vbcol=seagreen]
sure[vbcol=seagreen]
and[vbcol=seagreen]
your[vbcol=seagreen]
another[vbcol=seagreen]
database[vbcol=seagreen]
want[vbcol=seagreen]
I[vbcol=seagreen]
copy[vbcol=seagreen]|||Thanks for he additional information. Staging or temporary tables seems ver
y
useful. Not familiar with DTS packages imagine there is a bit of a learning
curve, will look under Query Analyser help. The process will only have to b
e
repeated one time but still be nice to have it in a script or package.
"John Bell" wrote:
> Hi Paul
> Now you are talking about dbase copy! I was talking about copying SQL Serv
er
> databases!!!
> In some cirumstances when alot of manipulation has to be done it is often
> easier to load everything into staging tables and then do whatever work is
> needed using them. A single DTS package may be created to do all of this i
s
> you wanted a reproducable process, this which is often the case as you may
> to several trial runs before you have the data in the form you want. It
> could also reduce the downtime when you do implement the final data
> migration.
>
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> to
> like
> copy
> backup/restore to
> and
> destination
> of
> of
> remaining 8
> the
> could
> sure
> and
> your
> another
> database
> want
> I
> copy
>
>
copying data from 1 table to another
just wondering the best way to copy data from one database to another after
doing a dbase port? The destination database works with a .NET application
and the source database came from a My SQL database (used a mysql driver and
access to get the data to SQL2000 dbase). Also the source database does not
have constraints setup but the the destination tables do, so just want to
fill the destination tables with the data from the source tables. I have the
Query Analizer tool so was thinking there may be an easy way to copy the data
using this tool.
Thanks.
--
Paul G
Software engineer.It depends with which tool you are the most confortable. Quick way could be
to use:
-insert into <destinationTable> select * from <SourceTable>. Make sure you
try a on a test database first.
You could also use the bulk copy utility. It depends how much data and how
many tables you have. Note that the first solution will increase your
transaction log size if you have a lot of data.
There is also DTS.
"Paul" wrote:
> just wondering the best way to copy data from one database to another after
> doing a dbase port? The destination database works with a .NET application
> and the source database came from a My SQL database (used a mysql driver and
> access to get the data to SQL2000 dbase). Also the source database does not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have the
> Query Analizer tool so was thinking there may be an easy way to copy the data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi
If the source database is outside SQL Server you may want to look at DTS or
the Import Wizard in Enterprise Manager (assuming that you don't have
MSDE!). If you save this as a package then it will be modifyable and
repeatable. If they are in the same database you can use INSERT...SELECT
statements (See Books online) to copy from one table to another. These could
be put into a script or DTS package so that you can run them when needed.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> just wondering the best way to copy data from one database to another
after
> doing a dbase port? The destination database works with a .NET
application
> and the source database came from a My SQL database (used a mysql driver
and
> access to get the data to SQL2000 dbase). Also the source database does
not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have
the
> Query Analizer tool so was thinking there may be an easy way to copy the
data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.|||Thanks for the information. The largest table has around 16k records of
about 10 fields, one other table has around 8k records and the remaining 8
tables are very small. Thinking of just using the query analyser with insert
into <database1.table1>
select * from <database2.table1> since I am somewhat comfortable with the
tool.
I am assuming you can copy from 1 dbase to another.
"Sasan Saidi" wrote:
> It depends with which tool you are the most confortable. Quick way could be
> to use:
> -insert into <destinationTable> select * from <SourceTable>. Make sure you
> try a on a test database first.
> You could also use the bulk copy utility. It depends how much data and how
> many tables you have. Note that the first solution will increase your
> transaction log size if you have a lot of data.
> There is also DTS.
>
> "Paul" wrote:
> > just wondering the best way to copy data from one database to another after
> > doing a dbase port? The destination database works with a .NET application
> > and the source database came from a My SQL database (used a mysql driver and
> > access to get the data to SQL2000 dbase). Also the source database does not
> > have constraints setup but the the destination tables do, so just want to
> > fill the destination tables with the data from the source tables. I have the
> > Query Analizer tool so was thinking there may be an easy way to copy the data
> > using this tool.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||Thanks for the response. The source database was MySQL but I now have it on
SQL2000 server, same server as the destination database. Will probably try
to use the insert, select statements, hopefully can use them copying between
databases. A DTS package seems to be quite useful but have not created any
before but will look into this in the future.
"John Bell" wrote:
> Hi
> If the source database is outside SQL Server you may want to look at DTS or
> the Import Wizard in Enterprise Manager (assuming that you don't have
> MSDE!). If you save this as a package then it will be modifyable and
> repeatable. If they are in the same database you can use INSERT...SELECT
> statements (See Books online) to copy from one table to another. These could
> be put into a script or DTS package so that you can run them when needed.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> > just wondering the best way to copy data from one database to another
> after
> > doing a dbase port? The destination database works with a .NET
> application
> > and the source database came from a My SQL database (used a mysql driver
> and
> > access to get the data to SQL2000 dbase). Also the source database does
> not
> > have constraints setup but the the destination tables do, so just want to
> > fill the destination tables with the data from the source tables. I have
> the
> > Query Analizer tool so was thinking there may be an easy way to copy the
> data
> > using this tool.
> > Thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Hi
There are several ways to copy a compelete database from one location to
another. The easiest if probably to backup/restore. If you backup/restore to
another database on the same computer then you will need to rename data and
log files (similarly if they are in a different location on the destination
computer you will have to do the same).
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
If you are doing this from Query Analyser check out the WITH MOVE syntax of
the RESTORE command in Books online.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> Thanks for the information. The largest table has around 16k records of
> about 10 fields, one other table has around 8k records and the remaining 8
> tables are very small. Thinking of just using the query analyser with
insert
> into <database1.table1>
> select * from <database2.table1> since I am somewhat comfortable with the
> tool.
> I am assuming you can copy from 1 dbase to another.
> "Sasan Saidi" wrote:
> > It depends with which tool you are the most confortable. Quick way could
be
> > to use:
> > -insert into <destinationTable> select * from <SourceTable>. Make sure
you
> > try a on a test database first.
> >
> > You could also use the bulk copy utility. It depends how much data and
how
> > many tables you have. Note that the first solution will increase your
> > transaction log size if you have a lot of data.
> >
> > There is also DTS.
> >
> >
> >
> > "Paul" wrote:
> >
> > > just wondering the best way to copy data from one database to another
after
> > > doing a dbase port? The destination database works with a .NET
application
> > > and the source database came from a My SQL database (used a mysql
driver and
> > > access to get the data to SQL2000 dbase). Also the source database
does not
> > > have constraints setup but the the destination tables do, so just want
to
> > > fill the destination tables with the data from the source tables. I
have the
> > > Query Analizer tool so was thinking there may be an easy way to copy
the data
> > > using this tool.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.|||Hi thanks for the additional information. Since I have made some changes to
a few of the tables, added additional fields and added table constraints like
(no duplicate records allowed) probably should not do a complete dbase copy
but good information to know.
"John Bell" wrote:
> Hi
> There are several ways to copy a compelete database from one location to
> another. The easiest if probably to backup/restore. If you backup/restore to
> another database on the same computer then you will need to rename data and
> log files (similarly if they are in a different location on the destination
> computer you will have to do the same).
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
> If you are doing this from Query Analyser check out the WITH MOVE syntax of
> the RESTORE command in Books online.
> John
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> > Thanks for the information. The largest table has around 16k records of
> > about 10 fields, one other table has around 8k records and the remaining 8
> > tables are very small. Thinking of just using the query analyser with
> insert
> > into <database1.table1>
> > select * from <database2.table1> since I am somewhat comfortable with the
> > tool.
> > I am assuming you can copy from 1 dbase to another.
> > "Sasan Saidi" wrote:
> >
> > > It depends with which tool you are the most confortable. Quick way could
> be
> > > to use:
> > > -insert into <destinationTable> select * from <SourceTable>. Make sure
> you
> > > try a on a test database first.
> > >
> > > You could also use the bulk copy utility. It depends how much data and
> how
> > > many tables you have. Note that the first solution will increase your
> > > transaction log size if you have a lot of data.
> > >
> > > There is also DTS.
> > >
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > just wondering the best way to copy data from one database to another
> after
> > > > doing a dbase port? The destination database works with a .NET
> application
> > > > and the source database came from a My SQL database (used a mysql
> driver and
> > > > access to get the data to SQL2000 dbase). Also the source database
> does not
> > > > have constraints setup but the the destination tables do, so just want
> to
> > > > fill the destination tables with the data from the source tables. I
> have the
> > > > Query Analizer tool so was thinking there may be an easy way to copy
> the data
> > > > using this tool.
> > > > Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
>
>|||Hi Paul
Now you are talking about dbase copy! I was talking about copying SQL Server
databases!!!
In some cirumstances when alot of manipulation has to be done it is often
easier to load everything into staging tables and then do whatever work is
needed using them. A single DTS package may be created to do all of this is
you wanted a reproducable process, this which is often the case as you may
to several trial runs before you have the data in the form you want. It
could also reduce the downtime when you do implement the final data
migration.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> Hi thanks for the additional information. Since I have made some changes
to
> a few of the tables, added additional fields and added table constraints
like
> (no duplicate records allowed) probably should not do a complete dbase
copy
> but good information to know.
> "John Bell" wrote:
> > Hi
> >
> > There are several ways to copy a compelete database from one location to
> > another. The easiest if probably to backup/restore. If you
backup/restore to
> > another database on the same computer then you will need to rename data
and
> > log files (similarly if they are in a different location on the
destination
> > computer you will have to do the same).
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
> >
> > If you are doing this from Query Analyser check out the WITH MOVE syntax
of
> > the RESTORE command in Books online.
> >
> > John
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> > > Thanks for the information. The largest table has around 16k records
of
> > > about 10 fields, one other table has around 8k records and the
remaining 8
> > > tables are very small. Thinking of just using the query analyser with
> > insert
> > > into <database1.table1>
> > > select * from <database2.table1> since I am somewhat comfortable with
the
> > > tool.
> > > I am assuming you can copy from 1 dbase to another.
> > > "Sasan Saidi" wrote:
> > >
> > > > It depends with which tool you are the most confortable. Quick way
could
> > be
> > > > to use:
> > > > -insert into <destinationTable> select * from <SourceTable>. Make
sure
> > you
> > > > try a on a test database first.
> > > >
> > > > You could also use the bulk copy utility. It depends how much data
and
> > how
> > > > many tables you have. Note that the first solution will increase
your
> > > > transaction log size if you have a lot of data.
> > > >
> > > > There is also DTS.
> > > >
> > > >
> > > >
> > > > "Paul" wrote:
> > > >
> > > > > just wondering the best way to copy data from one database to
another
> > after
> > > > > doing a dbase port? The destination database works with a .NET
> > application
> > > > > and the source database came from a My SQL database (used a mysql
> > driver and
> > > > > access to get the data to SQL2000 dbase). Also the source
database
> > does not
> > > > > have constraints setup but the the destination tables do, so just
want
> > to
> > > > > fill the destination tables with the data from the source tables.
I
> > have the
> > > > > Query Analizer tool so was thinking there may be an easy way to
copy
> > the data
> > > > > using this tool.
> > > > > Thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> >
> >
> >
> >|||Thanks for he additional information. Staging or temporary tables seems very
useful. Not familiar with DTS packages imagine there is a bit of a learning
curve, will look under Query Analyser help. The process will only have to be
repeated one time but still be nice to have it in a script or package.
"John Bell" wrote:
> Hi Paul
> Now you are talking about dbase copy! I was talking about copying SQL Server
> databases!!!
> In some cirumstances when alot of manipulation has to be done it is often
> easier to load everything into staging tables and then do whatever work is
> needed using them. A single DTS package may be created to do all of this is
> you wanted a reproducable process, this which is often the case as you may
> to several trial runs before you have the data in the form you want. It
> could also reduce the downtime when you do implement the final data
> migration.
>
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> > Hi thanks for the additional information. Since I have made some changes
> to
> > a few of the tables, added additional fields and added table constraints
> like
> > (no duplicate records allowed) probably should not do a complete dbase
> copy
> > but good information to know.
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > There are several ways to copy a compelete database from one location to
> > > another. The easiest if probably to backup/restore. If you
> backup/restore to
> > > another database on the same computer then you will need to rename data
> and
> > > log files (similarly if they are in a different location on the
> destination
> > > computer you will have to do the same).
> > >
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
> > >
> > > If you are doing this from Query Analyser check out the WITH MOVE syntax
> of
> > > the RESTORE command in Books online.
> > >
> > > John
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> > > > Thanks for the information. The largest table has around 16k records
> of
> > > > about 10 fields, one other table has around 8k records and the
> remaining 8
> > > > tables are very small. Thinking of just using the query analyser with
> > > insert
> > > > into <database1.table1>
> > > > select * from <database2.table1> since I am somewhat comfortable with
> the
> > > > tool.
> > > > I am assuming you can copy from 1 dbase to another.
> > > > "Sasan Saidi" wrote:
> > > >
> > > > > It depends with which tool you are the most confortable. Quick way
> could
> > > be
> > > > > to use:
> > > > > -insert into <destinationTable> select * from <SourceTable>. Make
> sure
> > > you
> > > > > try a on a test database first.
> > > > >
> > > > > You could also use the bulk copy utility. It depends how much data
> and
> > > how
> > > > > many tables you have. Note that the first solution will increase
> your
> > > > > transaction log size if you have a lot of data.
> > > > >
> > > > > There is also DTS.
> > > > >
> > > > >
> > > > >
> > > > > "Paul" wrote:
> > > > >
> > > > > > just wondering the best way to copy data from one database to
> another
> > > after
> > > > > > doing a dbase port? The destination database works with a .NET
> > > application
> > > > > > and the source database came from a My SQL database (used a mysql
> > > driver and
> > > > > > access to get the data to SQL2000 dbase). Also the source
> database
> > > does not
> > > > > > have constraints setup but the the destination tables do, so just
> want
> > > to
> > > > > > fill the destination tables with the data from the source tables.
> I
> > > have the
> > > > > > Query Analizer tool so was thinking there may be an easy way to
> copy
> > > the data
> > > > > > using this tool.
> > > > > > Thanks.
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.
> > >
> > >
> > >
> > >
>
>
doing a dbase port? The destination database works with a .NET application
and the source database came from a My SQL database (used a mysql driver and
access to get the data to SQL2000 dbase). Also the source database does not
have constraints setup but the the destination tables do, so just want to
fill the destination tables with the data from the source tables. I have the
Query Analizer tool so was thinking there may be an easy way to copy the data
using this tool.
Thanks.
--
Paul G
Software engineer.It depends with which tool you are the most confortable. Quick way could be
to use:
-insert into <destinationTable> select * from <SourceTable>. Make sure you
try a on a test database first.
You could also use the bulk copy utility. It depends how much data and how
many tables you have. Note that the first solution will increase your
transaction log size if you have a lot of data.
There is also DTS.
"Paul" wrote:
> just wondering the best way to copy data from one database to another after
> doing a dbase port? The destination database works with a .NET application
> and the source database came from a My SQL database (used a mysql driver and
> access to get the data to SQL2000 dbase). Also the source database does not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have the
> Query Analizer tool so was thinking there may be an easy way to copy the data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi
If the source database is outside SQL Server you may want to look at DTS or
the Import Wizard in Enterprise Manager (assuming that you don't have
MSDE!). If you save this as a package then it will be modifyable and
repeatable. If they are in the same database you can use INSERT...SELECT
statements (See Books online) to copy from one table to another. These could
be put into a script or DTS package so that you can run them when needed.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> just wondering the best way to copy data from one database to another
after
> doing a dbase port? The destination database works with a .NET
application
> and the source database came from a My SQL database (used a mysql driver
and
> access to get the data to SQL2000 dbase). Also the source database does
not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have
the
> Query Analizer tool so was thinking there may be an easy way to copy the
data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.|||Thanks for the information. The largest table has around 16k records of
about 10 fields, one other table has around 8k records and the remaining 8
tables are very small. Thinking of just using the query analyser with insert
into <database1.table1>
select * from <database2.table1> since I am somewhat comfortable with the
tool.
I am assuming you can copy from 1 dbase to another.
"Sasan Saidi" wrote:
> It depends with which tool you are the most confortable. Quick way could be
> to use:
> -insert into <destinationTable> select * from <SourceTable>. Make sure you
> try a on a test database first.
> You could also use the bulk copy utility. It depends how much data and how
> many tables you have. Note that the first solution will increase your
> transaction log size if you have a lot of data.
> There is also DTS.
>
> "Paul" wrote:
> > just wondering the best way to copy data from one database to another after
> > doing a dbase port? The destination database works with a .NET application
> > and the source database came from a My SQL database (used a mysql driver and
> > access to get the data to SQL2000 dbase). Also the source database does not
> > have constraints setup but the the destination tables do, so just want to
> > fill the destination tables with the data from the source tables. I have the
> > Query Analizer tool so was thinking there may be an easy way to copy the data
> > using this tool.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||Thanks for the response. The source database was MySQL but I now have it on
SQL2000 server, same server as the destination database. Will probably try
to use the insert, select statements, hopefully can use them copying between
databases. A DTS package seems to be quite useful but have not created any
before but will look into this in the future.
"John Bell" wrote:
> Hi
> If the source database is outside SQL Server you may want to look at DTS or
> the Import Wizard in Enterprise Manager (assuming that you don't have
> MSDE!). If you save this as a package then it will be modifyable and
> repeatable. If they are in the same database you can use INSERT...SELECT
> statements (See Books online) to copy from one table to another. These could
> be put into a script or DTS package so that you can run them when needed.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> > just wondering the best way to copy data from one database to another
> after
> > doing a dbase port? The destination database works with a .NET
> application
> > and the source database came from a My SQL database (used a mysql driver
> and
> > access to get the data to SQL2000 dbase). Also the source database does
> not
> > have constraints setup but the the destination tables do, so just want to
> > fill the destination tables with the data from the source tables. I have
> the
> > Query Analizer tool so was thinking there may be an easy way to copy the
> data
> > using this tool.
> > Thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Hi
There are several ways to copy a compelete database from one location to
another. The easiest if probably to backup/restore. If you backup/restore to
another database on the same computer then you will need to rename data and
log files (similarly if they are in a different location on the destination
computer you will have to do the same).
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
If you are doing this from Query Analyser check out the WITH MOVE syntax of
the RESTORE command in Books online.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> Thanks for the information. The largest table has around 16k records of
> about 10 fields, one other table has around 8k records and the remaining 8
> tables are very small. Thinking of just using the query analyser with
insert
> into <database1.table1>
> select * from <database2.table1> since I am somewhat comfortable with the
> tool.
> I am assuming you can copy from 1 dbase to another.
> "Sasan Saidi" wrote:
> > It depends with which tool you are the most confortable. Quick way could
be
> > to use:
> > -insert into <destinationTable> select * from <SourceTable>. Make sure
you
> > try a on a test database first.
> >
> > You could also use the bulk copy utility. It depends how much data and
how
> > many tables you have. Note that the first solution will increase your
> > transaction log size if you have a lot of data.
> >
> > There is also DTS.
> >
> >
> >
> > "Paul" wrote:
> >
> > > just wondering the best way to copy data from one database to another
after
> > > doing a dbase port? The destination database works with a .NET
application
> > > and the source database came from a My SQL database (used a mysql
driver and
> > > access to get the data to SQL2000 dbase). Also the source database
does not
> > > have constraints setup but the the destination tables do, so just want
to
> > > fill the destination tables with the data from the source tables. I
have the
> > > Query Analizer tool so was thinking there may be an easy way to copy
the data
> > > using this tool.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.|||Hi thanks for the additional information. Since I have made some changes to
a few of the tables, added additional fields and added table constraints like
(no duplicate records allowed) probably should not do a complete dbase copy
but good information to know.
"John Bell" wrote:
> Hi
> There are several ways to copy a compelete database from one location to
> another. The easiest if probably to backup/restore. If you backup/restore to
> another database on the same computer then you will need to rename data and
> log files (similarly if they are in a different location on the destination
> computer you will have to do the same).
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
> If you are doing this from Query Analyser check out the WITH MOVE syntax of
> the RESTORE command in Books online.
> John
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> > Thanks for the information. The largest table has around 16k records of
> > about 10 fields, one other table has around 8k records and the remaining 8
> > tables are very small. Thinking of just using the query analyser with
> insert
> > into <database1.table1>
> > select * from <database2.table1> since I am somewhat comfortable with the
> > tool.
> > I am assuming you can copy from 1 dbase to another.
> > "Sasan Saidi" wrote:
> >
> > > It depends with which tool you are the most confortable. Quick way could
> be
> > > to use:
> > > -insert into <destinationTable> select * from <SourceTable>. Make sure
> you
> > > try a on a test database first.
> > >
> > > You could also use the bulk copy utility. It depends how much data and
> how
> > > many tables you have. Note that the first solution will increase your
> > > transaction log size if you have a lot of data.
> > >
> > > There is also DTS.
> > >
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > just wondering the best way to copy data from one database to another
> after
> > > > doing a dbase port? The destination database works with a .NET
> application
> > > > and the source database came from a My SQL database (used a mysql
> driver and
> > > > access to get the data to SQL2000 dbase). Also the source database
> does not
> > > > have constraints setup but the the destination tables do, so just want
> to
> > > > fill the destination tables with the data from the source tables. I
> have the
> > > > Query Analizer tool so was thinking there may be an easy way to copy
> the data
> > > > using this tool.
> > > > Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
>
>|||Hi Paul
Now you are talking about dbase copy! I was talking about copying SQL Server
databases!!!
In some cirumstances when alot of manipulation has to be done it is often
easier to load everything into staging tables and then do whatever work is
needed using them. A single DTS package may be created to do all of this is
you wanted a reproducable process, this which is often the case as you may
to several trial runs before you have the data in the form you want. It
could also reduce the downtime when you do implement the final data
migration.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> Hi thanks for the additional information. Since I have made some changes
to
> a few of the tables, added additional fields and added table constraints
like
> (no duplicate records allowed) probably should not do a complete dbase
copy
> but good information to know.
> "John Bell" wrote:
> > Hi
> >
> > There are several ways to copy a compelete database from one location to
> > another. The easiest if probably to backup/restore. If you
backup/restore to
> > another database on the same computer then you will need to rename data
and
> > log files (similarly if they are in a different location on the
destination
> > computer you will have to do the same).
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
> >
> > If you are doing this from Query Analyser check out the WITH MOVE syntax
of
> > the RESTORE command in Books online.
> >
> > John
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> > > Thanks for the information. The largest table has around 16k records
of
> > > about 10 fields, one other table has around 8k records and the
remaining 8
> > > tables are very small. Thinking of just using the query analyser with
> > insert
> > > into <database1.table1>
> > > select * from <database2.table1> since I am somewhat comfortable with
the
> > > tool.
> > > I am assuming you can copy from 1 dbase to another.
> > > "Sasan Saidi" wrote:
> > >
> > > > It depends with which tool you are the most confortable. Quick way
could
> > be
> > > > to use:
> > > > -insert into <destinationTable> select * from <SourceTable>. Make
sure
> > you
> > > > try a on a test database first.
> > > >
> > > > You could also use the bulk copy utility. It depends how much data
and
> > how
> > > > many tables you have. Note that the first solution will increase
your
> > > > transaction log size if you have a lot of data.
> > > >
> > > > There is also DTS.
> > > >
> > > >
> > > >
> > > > "Paul" wrote:
> > > >
> > > > > just wondering the best way to copy data from one database to
another
> > after
> > > > > doing a dbase port? The destination database works with a .NET
> > application
> > > > > and the source database came from a My SQL database (used a mysql
> > driver and
> > > > > access to get the data to SQL2000 dbase). Also the source
database
> > does not
> > > > > have constraints setup but the the destination tables do, so just
want
> > to
> > > > > fill the destination tables with the data from the source tables.
I
> > have the
> > > > > Query Analizer tool so was thinking there may be an easy way to
copy
> > the data
> > > > > using this tool.
> > > > > Thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> >
> >
> >
> >|||Thanks for he additional information. Staging or temporary tables seems very
useful. Not familiar with DTS packages imagine there is a bit of a learning
curve, will look under Query Analyser help. The process will only have to be
repeated one time but still be nice to have it in a script or package.
"John Bell" wrote:
> Hi Paul
> Now you are talking about dbase copy! I was talking about copying SQL Server
> databases!!!
> In some cirumstances when alot of manipulation has to be done it is often
> easier to load everything into staging tables and then do whatever work is
> needed using them. A single DTS package may be created to do all of this is
> you wanted a reproducable process, this which is often the case as you may
> to several trial runs before you have the data in the form you want. It
> could also reduce the downtime when you do implement the final data
> migration.
>
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> > Hi thanks for the additional information. Since I have made some changes
> to
> > a few of the tables, added additional fields and added table constraints
> like
> > (no duplicate records allowed) probably should not do a complete dbase
> copy
> > but good information to know.
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > There are several ways to copy a compelete database from one location to
> > > another. The easiest if probably to backup/restore. If you
> backup/restore to
> > > another database on the same computer then you will need to rename data
> and
> > > log files (similarly if they are in a different location on the
> destination
> > > computer you will have to do the same).
> > >
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
> > >
> > > If you are doing this from Query Analyser check out the WITH MOVE syntax
> of
> > > the RESTORE command in Books online.
> > >
> > > John
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> > > > Thanks for the information. The largest table has around 16k records
> of
> > > > about 10 fields, one other table has around 8k records and the
> remaining 8
> > > > tables are very small. Thinking of just using the query analyser with
> > > insert
> > > > into <database1.table1>
> > > > select * from <database2.table1> since I am somewhat comfortable with
> the
> > > > tool.
> > > > I am assuming you can copy from 1 dbase to another.
> > > > "Sasan Saidi" wrote:
> > > >
> > > > > It depends with which tool you are the most confortable. Quick way
> could
> > > be
> > > > > to use:
> > > > > -insert into <destinationTable> select * from <SourceTable>. Make
> sure
> > > you
> > > > > try a on a test database first.
> > > > >
> > > > > You could also use the bulk copy utility. It depends how much data
> and
> > > how
> > > > > many tables you have. Note that the first solution will increase
> your
> > > > > transaction log size if you have a lot of data.
> > > > >
> > > > > There is also DTS.
> > > > >
> > > > >
> > > > >
> > > > > "Paul" wrote:
> > > > >
> > > > > > just wondering the best way to copy data from one database to
> another
> > > after
> > > > > > doing a dbase port? The destination database works with a .NET
> > > application
> > > > > > and the source database came from a My SQL database (used a mysql
> > > driver and
> > > > > > access to get the data to SQL2000 dbase). Also the source
> database
> > > does not
> > > > > > have constraints setup but the the destination tables do, so just
> want
> > > to
> > > > > > fill the destination tables with the data from the source tables.
> I
> > > have the
> > > > > > Query Analizer tool so was thinking there may be an easy way to
> copy
> > > the data
> > > > > > using this tool.
> > > > > > Thanks.
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.
> > >
> > >
> > >
> > >
>
>
Wednesday, March 7, 2012
Copying and moving tasks between DTSX
hi,
When you copy a sequence container between dtsx you obtain a different size at destination. Any way to avoid this? I'd like to see that task with the same size.
Maybe some posh from my side...
No, you'll have to resize it yourself after pasting I'm afraid.
-Jamie
Copying an ntext cell from one cell into another (destination row already exists
Hi!
What I'd like to do is:
UPDATE table1
SET
A_TEXT_COLUMN = (SELECT another_text_column
FROM table2
WHERE table2_id = @.precomputed_id_1)
WHERE table1_ID = @.precomputed_id_2
Since the cells are text, this does not work. Since the cell to be updated is in an already exitant row, it's not possible to simply use insert.
I'd like to do something like (PSEUDOcode):
WRITETEXT(table1.A_TEXT_COLUMN, READTEXT(@.textptr_initialised_to_point_at_target_c ell))
But the *actual* synatx of WRITETEXT and READTEXT seem totally inappropriate for any such trick...
Any hints or pointers HUGELY appreciated... THANXIs this a standard *problem*? Or an unsolved one (just an sqlserver limitation that must be dealt with in a totally different way??)
I saw 10 people read the thread but nobody answered, maybe the problem is a generally troublesome one with no good solutions...
Any ideas at all?
Thanx,
wOAst
What I'd like to do is:
UPDATE table1
SET
A_TEXT_COLUMN = (SELECT another_text_column
FROM table2
WHERE table2_id = @.precomputed_id_1)
WHERE table1_ID = @.precomputed_id_2
Since the cells are text, this does not work. Since the cell to be updated is in an already exitant row, it's not possible to simply use insert.
I'd like to do something like (PSEUDOcode):
WRITETEXT(table1.A_TEXT_COLUMN, READTEXT(@.textptr_initialised_to_point_at_target_c ell))
But the *actual* synatx of WRITETEXT and READTEXT seem totally inappropriate for any such trick...
Any hints or pointers HUGELY appreciated... THANXIs this a standard *problem*? Or an unsolved one (just an sqlserver limitation that must be dealt with in a totally different way??)
I saw 10 people read the thread but nobody answered, maybe the problem is a generally troublesome one with no good solutions...
Any ideas at all?
Thanx,
wOAst
Saturday, February 25, 2012
Copying a database to a different SQL Server
Hi,
I have a SQL Server 2000 database. I want to copy the database to a
different SQL server 2000. The destination sever does not at all have that
database. How can I copy the database schema and the all the data stored in
the database.
Any help regarding this will be highly appreciated.
Thank,
Vinita"Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
news:#paERIR4DHA.2304@.TK2MSFTNGP10.phx.gbl...
that
in
The best way is to use a current BACKUP from your source server and do a
RESTORE to your target server. See BOL for syntax.
You could experiment with the copy database wizard from Enterprise manager
on your target server, right click databases> all tasks > copy database
wizard. I have to caution you I have not used it -- I prefer to do things
the old fashioned way.
Steve|||Watch out if you need to move logins and users, see this page -
http://support.microsoft.com/defaul...;EN-US;Q246133&
Ray Higdon MCSE, MCDBA, CCNA
--
"Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
news:%23paERIR4DHA.2304@.TK2MSFTNGP10.phx.gbl...
that
in
Make sure you run the sp_help_revlogin to bring over your logins properly.
Lately I been experimenting with DTS and I am fascinated by its power.
Steven S. Warren
MCSA, MCSE, MCDBA, CCA, CIW-SA, CIW-MA, NETWORK+, i-NET+
http:\\www.swtechworks.com
This posting is provided AS IS with no warranties, and confers no rights.
"Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
news:%23paERIR4DHA.2304@.TK2MSFTNGP10.phx.gbl...
that
in
In the Enteprise manager, right click on the database and go to "All Tasks".
Then click on Detach Database... and click Ok. The database will disappear
from the list. Now simply go to the drive where the database is stored (In m
y case it's C:\Program File
s\Microsoft SQL Server\MSSQL\Data). Now select the database MDF and LDF File
s and copy them to the destination machine. Then do the same steps, right cl
ick on the database and go to "All Tasks", and now click on Attach Database.
It will make you select th
e database file and point it to the DatabaseName_Data.MDF file that you just
copied. Thats it. It should then appear on the list. If you got any questio
ns email.
Danny Cabrera
dcabrera@.medstarholdings.com
System Programmer|||Thanks a lot ...
It worked.
Vinita
"Danny" <dcabrera@.medstarholdings.com> wrote in message
news:E3310419-761C-4EE9-9696-2D54EB283D1B@.microsoft.com...
Tasks". Then click on Detach Database... and click Ok. The database will
disappear from the list. Now simply go to the drive where the database is
stored (In my case it's C:\Program Files\Microsoft SQL Server\MSSQL\Data).
Now select the database MDF and LDF Files and copy them to the destination
machine. Then do the same steps, right click on the database and go to "All
Tasks", and now click on Attach Database. It will make you select the
database file and point it to the DatabaseName_Data.MDF file that you just
copied. Thats it. It should then appear on the list. If you got any
questions email.
I have a SQL Server 2000 database. I want to copy the database to a
different SQL server 2000. The destination sever does not at all have that
database. How can I copy the database schema and the all the data stored in
the database.
Any help regarding this will be highly appreciated.
Thank,
Vinita"Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
news:#paERIR4DHA.2304@.TK2MSFTNGP10.phx.gbl...
quote:
> I have a SQL Server 2000 database. I want to copy the database to a
> different SQL server 2000. The destination sever does not at all have
that
quote:
> database. How can I copy the database schema and the all the data stored
in
quote:
> the database.
The best way is to use a current BACKUP from your source server and do a
RESTORE to your target server. See BOL for syntax.
You could experiment with the copy database wizard from Enterprise manager
on your target server, right click databases> all tasks > copy database
wizard. I have to caution you I have not used it -- I prefer to do things
the old fashioned way.
Steve|||Watch out if you need to move logins and users, see this page -
http://support.microsoft.com/defaul...;EN-US;Q246133&
Ray Higdon MCSE, MCDBA, CCNA
--
"Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
news:%23paERIR4DHA.2304@.TK2MSFTNGP10.phx.gbl...
quote:
> Hi,
> I have a SQL Server 2000 database. I want to copy the database to a
> different SQL server 2000. The destination sever does not at all have
that
quote:
> database. How can I copy the database schema and the all the data stored
in
quote:|||I also prefer the old fashioned way. I like the ability to control things.
> the database.
> Any help regarding this will be highly appreciated.
> Thank,
> Vinita
>
Make sure you run the sp_help_revlogin to bring over your logins properly.
Lately I been experimenting with DTS and I am fascinated by its power.
Steven S. Warren
MCSA, MCSE, MCDBA, CCA, CIW-SA, CIW-MA, NETWORK+, i-NET+
http:\\www.swtechworks.com
This posting is provided AS IS with no warranties, and confers no rights.
"Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
news:%23paERIR4DHA.2304@.TK2MSFTNGP10.phx.gbl...
quote:
> Hi,
> I have a SQL Server 2000 database. I want to copy the database to a
> different SQL server 2000. The destination sever does not at all have
that
quote:
> database. How can I copy the database schema and the all the data stored
in
quote:|||Hey Vinita,
> the database.
> Any help regarding this will be highly appreciated.
> Thank,
> Vinita
>
In the Enteprise manager, right click on the database and go to "All Tasks".
Then click on Detach Database... and click Ok. The database will disappear
from the list. Now simply go to the drive where the database is stored (In m
y case it's C:\Program File
s\Microsoft SQL Server\MSSQL\Data). Now select the database MDF and LDF File
s and copy them to the destination machine. Then do the same steps, right cl
ick on the database and go to "All Tasks", and now click on Attach Database.
It will make you select th
e database file and point it to the DatabaseName_Data.MDF file that you just
copied. Thats it. It should then appear on the list. If you got any questio
ns email.
Danny Cabrera
dcabrera@.medstarholdings.com
System Programmer|||Thanks a lot ...
It worked.
Vinita
"Danny" <dcabrera@.medstarholdings.com> wrote in message
news:E3310419-761C-4EE9-9696-2D54EB283D1B@.microsoft.com...
quote:
> Hey Vinita,
> In the Enteprise manager, right click on the database and go to "All
Tasks". Then click on Detach Database... and click Ok. The database will
disappear from the list. Now simply go to the drive where the database is
stored (In my case it's C:\Program Files\Microsoft SQL Server\MSSQL\Data).
Now select the database MDF and LDF Files and copy them to the destination
machine. Then do the same steps, right click on the database and go to "All
Tasks", and now click on Attach Database. It will make you select the
database file and point it to the DatabaseName_Data.MDF file that you just
copied. Thats it. It should then appear on the list. If you got any
questions email.
quote:
> Danny Cabrera
> dcabrera@.medstarholdings.com
> System Programmer
Friday, February 24, 2012
copy wizard gives error creating share
When I run the database copy wizard I'm getting an error creating shared omwwize.
The source server is 7.0. The destination server is 2000. I'm invoking the wizard from the 2000 machine.
Anybody know what this error means?
Thanks
SharonIf the destination SQL Server is started under a domain account that does not have local administrator privileges on the source computer then the following error message may be generated by the wizard:
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Unspecified error
Step Error code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Clicking on the "MORE INFO >>>" button, you will find that permissions to create the remote share are not present.
Failed to create the share OMWWIZD
The source server is 7.0. The destination server is 2000. I'm invoking the wizard from the 2000 machine.
Anybody know what this error means?
Thanks
SharonIf the destination SQL Server is started under a domain account that does not have local administrator privileges on the source computer then the following error message may be generated by the wizard:
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Unspecified error
Step Error code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Clicking on the "MORE INFO >>>" button, you will find that permissions to create the remote share are not present.
Failed to create the share OMWWIZD
Copy Wizard Failure when coping a database on the same machine
I am trying to copy the a database on the same machine (source and destination are the same). The wizard fails using the SMO method. I have attemped to turn on the "Save Transfer logs", but it does not appear to save a file: I have looked in C:\Documents and Settings\<user-name>\Local Settings\Application Data\Microsoft\SQL Server\SMO for all the users on the server. None of them have the SMO directory in the that path nor can I find a SMO directory on the server.
The SQL Server agent account is set to Local System.
I have made the Local Administrator a Sysadmin in SQL server.
Job History after failure:
Date 6/15/2007 8:25:10 PM
Log Job History (CDW_YOUR-BQEHG1XTVQ_YOUR-BQEHG1XTVQ_9)
Step ID 1
Server YOUR-BQEHG1XTVQ
Job Name CDW_YOUR-BQEHG1XTVQ_YOUR-BQEHG1XTVQ_9
Step Name CDW_YOUR-BQEHG1XTVQ_YOUR-BQEHG1XTVQ_9_Step
Duration 00:00:48
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: YOUR-BQEHG1XTVQ\SYSTEM. ...GUE SQL USE [Tango_QA] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[strpcd_firewall_lan_objects_to_add]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[strpcd_firewall_lan_objects_to_add] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stpcd_call_view_your_account_firewall_policies]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[stpcd_call_view_your_account_firewall_policies] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stpcd_call_view_your_account_firewall_objects]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[stpcd_call_view_your_account_firewall_objects] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stpcd_GetSIPNetworkLocation]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[stpcd_GetSIPNetworkLocation] GO IF EXISTS (SELECT * FROM sys.objects WHERE obje... The package execution fa... The step failed.
Stumped as what what to try next or what the issue is?
What is the level of service pack on SQL Server?
BOL refers:
The destination server must be running SQL Server 2005 Service Pack 2 or a later version. The computer on which the Copy Database Wizard runs may be the source or destination server, or a separate computer. This computer must also be running SQL Server 2005 Service Pack 2 or a later version to use all the features of the wizard.
To use the Copy Database Wizard, you must be a member of the sysadmin fixed server role on the source and destination servers. To transfer databases by using the detach-and-attach method, you must have file system access to the file-system share that contains the source database files.
|||The source and the destination server are one and the same. It is running SQL 2005 SP2. I am using SA to make a copy of the database. The wizard starts to copy the database but then fails before coping all of the tables.|||Ensure SA login is not disabled, you might try using SQL SErver service account in this case.
copy two table
hi,
How copy two table between two database?
copy 1 table1 to 2 table2
1 - cursor number,
2 - cursor number,
table1 - sourse table
table2 - destination table
Jaromi"ja" <jaromi111@.poczta.onet.pl> ???/???? ? ???? ???:
news:1etlgtor6kjsh.bx223caeyi5p$.dlg@.40tude.net...
> hi,
> How copy two table between two database?
> copy 1 table1 to 2 table2
> 1 - cursor number,
> 2 - cursor number,
> table1 - sourse table
> table2 - destination table
> Jaromi
Right click on table in EM -> all tasks -> export data...|||On Sun, 15 Aug 2004 20:42:08 +0200, ja wrote:
>hi,
>How copy two table between two database?
>copy 1 table1 to 2 table2
>1 - cursor number,
>2 - cursor number,
>table1 - sourse table
>table2 - destination table
>Jaromi
Hi Jaromi,
Probably something like this:
INSERT table2 (column1, column2, ...)
SELECT column1, column2, ...
FROM table1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)