SqlServer2005: Transfer Sql Server Objects

[Mark Olbert]

I'm looking for help in getting the Transfer Sql Server Objects task to work. The goal is to transfer the tables, views, sprocs,
udfs from a SqlServer 2000 database to a Sql Server 2005 database.

When I set the task up and run it, it fails complaining about an invalid foreign key relation. The relation in question is valid,
and used in the current production environment under SS2000, so I think the real problem is something else. I've seen reports that
this kind of error occurs when the Transfer task encounters a foreign key involving a table that hasn't been transferred yet.

As an aside, this kind of transfer worked flawlessly under Enterprise Manager when I used the DTS Wizard to move the objects between
two instances of SqlServer2000. Unfortunately, Enterprise Manager doesn't appear to work with SqlServer2005.

Any help and/or pointers would be greatly appreciated (including other strategies on how to affect the transfer -- I tried using the
Transfer Database task, too, but it failed when it supposedly couldn't find the path on the target server -- I say supposedly
because it was able to find the path in order to delete the pre-existing files on the target server)!

- Mark

[Erland Sommarskog]

Mark Olbert (Ch*********@newsgroups.nospam) writes:

Any help and/or pointers would be greatly appreciated (including other
strategies on how to affect the transfer

The best is of course to keep all database objects under version control.
In such case, you never have to script anything, you just get what you
need from the version-control system. You would still have to move the
data, but bcp is usually a good choice for this.

In lieu of a source-control system to get the source from, you can use
Generate Scrips from the Object Explorer in Mgmt Studio. Select the
database, right-click and then to the Tasks submenu. Once the tables
are up on the other side, you can use BCP to move the data.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Mark Olbert]

>In lieu of a source-control system to get the source from, you can use

>Generate Scrips from the Object Explorer in Mgmt Studio. Select the
database, right-click and then to the Tasks submenu. Once the tables
are up on the other side, you can use BCP to move the data.

I'm not that familiar with BCP (I tended to just use the DTS Wizard in Enterprise Manager), but I can read up on it.

Do you have any references on using version control with SqlServer 2000 and 2005? I'm not familiar with using version control on
database objects.

Thanks for the quick reply!

- Mark

[Erland Sommarskog]

Mark Olbert (Ch*********@newsgroups.nospam) writes:

Do you have any references on using version control with SqlServer 2000
and 2005? I'm not familiar with using version control on database
objects.

If you are familliar to version control, you are familiar to version control
of database objects: you check out, edit, compile and test until you are
satisfied and then you check in. When you need to set a baseline for
delivering to test or production, you set a label so that you know what
you shipped.

Of course tables are a little special, since you don't test them in the
same manner, and when you change a table you will need to compose an
upgrade script to preserve the data in some way, but that you need to do
without version control as well.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Mark Olbert (Ch*********@newsgroups.nospam) writes:

I'm not that familiar with BCP (I tended to just use the DTS Wizard in
Enterprise Manager), but I can read up on it.

As a quick start here is a sample command exporting data out:

bcp database..tbl out tbl.bcp -n -T -S myserver

-n specifies native format, which is the best for moving data between two
SQL Server databases in most cases. -T specifies trusted connection. Use
-U and -P to specify username and password for SQL authentication if
that is what you use.

For importing data to the other server, just change "out" to "in" and the
server name of course. ...but if the table has an identity column you
also need to specify -E to use the values in the file.

If the databases have different collation you need to use a format file.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx