Moving tables in SQL Server 2005 from one server to another

I have been working with databases for nearly 10 years now, but for
the life of me I can't figure out how to move database tables from one
server to another in SQL Server 2005 without using scripts.
I used to use DTS in SQL Server 2000 and everything seemed to go
smoothly. I used a combination of a Visio document (to drop and
recreate constraints, add columns, change defaults, etc) and DTS to
manage my whole deployment.
Now I am trying these tasks in SQL Server 2005 and while Visio still
can keep the structure of my tables in sync, I am having issues with
the replacement tool for DTS.
I tried using SSIS to move the data, and this is a description of the
issues I am having. I can't find any posts from anybody having these
issues, and I really don't understand why because I am getting the
same results on 2 different installations of SQL Server 2005.
1. I start by going through the menu and clicking the "import data" or
"export data" command.
2. I choose the source and destination database. I use the "SQL Native
Client" driver.
3. I choose the "Copy data from one or more tables or views" option.
4. I choose my table and give it a name in the destination database.
5. I click the "Edit Mappings" button, and choose the option to delete
rows in destination table, and check the box for "enable identity
insert".
6. Then I choose to execute the package immediately.
When I follow these exact steps, I get the following results:
1. The destination data is not removed from the table before the data
from the source is inserted.
2. The data that is inserted is renumbered starting from the last
index of the table, rather than using the original identity values.
Can somebody please explain to me why I can't get the exact same data
into another table in another database like I could in the previous
version of SQL Server?
If I have to switch to scripts to do this I will even though I much
prefer the GUI. If someone can suggest an easy way to generate the
scripts, it would be helpful too.Perhaps you aren't using the same functionality in SSIS as you were using in DTS?
Where you using the "Copy data and objects between SQL Sever databases" in DTS?
If so, this isn't available in the 2005 wizard. You have to create a SSIS project using "Business
Intelligence Development Studio" (which is Visual Studio), and you will find this task type here.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188458567.638491.35330@.m37g2000prh.googlegroups.com...
>I have been working with databases for nearly 10 years now, but for
> the life of me I can't figure out how to move database tables from one
> server to another in SQL Server 2005 without using scripts.
> I used to use DTS in SQL Server 2000 and everything seemed to go
> smoothly. I used a combination of a Visio document (to drop and
> recreate constraints, add columns, change defaults, etc) and DTS to
> manage my whole deployment.
> Now I am trying these tasks in SQL Server 2005 and while Visio still
> can keep the structure of my tables in sync, I am having issues with
> the replacement tool for DTS.
> I tried using SSIS to move the data, and this is a description of the
> issues I am having. I can't find any posts from anybody having these
> issues, and I really don't understand why because I am getting the
> same results on 2 different installations of SQL Server 2005.
> 1. I start by going through the menu and clicking the "import data" or
> "export data" command.
> 2. I choose the source and destination database. I use the "SQL Native
> Client" driver.
> 3. I choose the "Copy data from one or more tables or views" option.
> 4. I choose my table and give it a name in the destination database.
> 5. I click the "Edit Mappings" button, and choose the option to delete
> rows in destination table, and check the box for "enable identity
> insert".
> 6. Then I choose to execute the package immediately.
> When I follow these exact steps, I get the following results:
> 1. The destination data is not removed from the table before the data
> from the source is inserted.
> 2. The data that is inserted is renumbered starting from the last
> index of the table, rather than using the original identity values.
> Can somebody please explain to me why I can't get the exact same data
> into another table in another database like I could in the previous
> version of SQL Server?
> If I have to switch to scripts to do this I will even though I much
> prefer the GUI. If someone can suggest an easy way to generate the
> scripts, it would be helpful too.
>|||On Aug 30, 12:38 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Perhaps you aren't using the same functionality in SSIS as you were using in DTS?
> Where you using the "Copy data and objects between SQL Sever databases" in DTS?
> If so, this isn't available in the 2005 wizard. You have to create a SSIS project using "Business
> Intelligence Development Studio" (which is Visual Studio), and you will find this task type here.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
Thanks for the tip Tibor.
Yes, I used to use the 'Copy data and objects" option. Do you have a
link to the documentation that shows how to create this task in SQL
Server 2005 using Business Intelligence Development Studio? This type
of thing used to take 5 minutes to do, I hope it is the same now...|||This BOL link of probably a good start:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/ececa4c4-1880-4d57-ac5a-00c4ea92a632.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188460612.048157.157090@.i13g2000prf.googlegroups.com...
> On Aug 30, 12:38 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Perhaps you aren't using the same functionality in SSIS as you were using in DTS?
>> Where you using the "Copy data and objects between SQL Sever databases" in DTS?
>> If so, this isn't available in the 2005 wizard. You have to create a SSIS project using "Business
>> Intelligence Development Studio" (which is Visual Studio), and you will find this task type here.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> Thanks for the tip Tibor.
> Yes, I used to use the 'Copy data and objects" option. Do you have a
> link to the documentation that shows how to create this task in SQL
> Server 2005 using Business Intelligence Development Studio? This type
> of thing used to take 5 minutes to do, I hope it is the same now...
>|||On Aug 30, 1:21 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This BOL link of probably a good start:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/ececa4c4-1880-4d57-ac5=a-=AD00c4ea92a632.htm
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asp=
http://sqlblog.com/blogs/tibor_karaszi
>
Thanks. What BOL version do I need? That link doesn't work for me.|||I'm on (May 2007) SQL Server 2005 Books Online, as in the title of the BOL window.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188468165.952853.123760@.l22g2000prc.googlegroups.com...
On Aug 30, 1:21 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This BOL link of probably a good start:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/ececa4c4-1880-4d57-ac5a-­00c4ea92a632.htm
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
Thanks. What BOL version do I need? That link doesn't work for me.|||I think you omit some part of it cause it works for me. Don't omit "ms-help"
or anything, it's a whole address.
--
Ekrem Önsoy
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188468165.952853.123760@.l22g2000prc.googlegroups.com...
On Aug 30, 1:21 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This BOL link of probably a good start:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/ececa4c4-1880-4d57-ac5a-­00c4ea92a632.htm
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
Thanks. What BOL version do I need? That link doesn't work for me.|||On Aug 30, 3:08 am, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> I think you omit some part of it cause it works for me. Don't omit "ms-he=lp"
> or anything, it's a whole address.
>
I copied and pasted the link exactly into SQL Server 2005 books
online. I also double checked I have May 2007 version. This is the
result I get:
This program cannot display the webpage
Most likely causes:
You are not connected to the Internet.
The website is encountering problems.
There might be a typing error in the address.
What you can try:
Check your Internet connection. Try visiting another website to
make sure you are connected.
Retype the address.
Go back to the previous page.
More information
---
Of course, if I am not connected to the Internet, how am I able to
post this? :) Do you have an MSDN link to the same document? Thanks.|||Strange...
Here's the Internet/MSDN URL:
http://msdn2.microsoft.com/en-us/library/ms141823.aspx
Can you navigate to the same topic in your local SQL Server BOL and post the URL you have there? I'm
worried if there are two versions of BOL out there with different URL structures...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188470148.019177.12830@.x40g2000prg.googlegroups.com...
On Aug 30, 3:08 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> I think you omit some part of it cause it works for me. Don't omit "ms-help"
> or anything, it's a whole address.
>
I copied and pasted the link exactly into SQL Server 2005 books
online. I also double checked I have May 2007 version. This is the
result I get:
This program cannot display the webpage
Most likely causes:
You are not connected to the Internet.
The website is encountering problems.
There might be a typing error in the address.
What you can try:
Check your Internet connection. Try visiting another website to
make sure you are connected.
Retype the address.
Go back to the previous page.
More information
---
Of course, if I am not connected to the Internet, how am I able to
post this? :) Do you have an MSDN link to the same document? Thanks.|||Weird - I think it is something having to do with either the browser
or this forum, most likely related to character sets. When I copied
and pasted the link into my browser, it came up as a bad link, but
when I searched for the title of the document, I got it and it came up
with the same URL you posted. Here it is:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/ececa4c4-1880-4d57-
ac5a-00c4ea92a632.htm
I did notice when I copied and pasted the URL, it had a sequence of 3
dashes in a row in it, which isn't in the one you posted so it appears
that the copy on the clipboard was being converted somehow.
Anyway, I will take a look at the article to learn about this new
method of doing deployment. Thanks for the assistance with this.
I would still like to know why SSIS just doesn't even seem to function
though.
On Aug 30, 3:51 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Strange...
> Here's the Internet/MSDN URL:http://msdn2.microsoft.com/en-us/library/ms141823.aspx
> Can you navigate to the same topic in your local SQL Server BOL and post the URL you have there? I'm
> worried if there are two versions of BOL out there with different URL structures...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>|||> I would still like to know why SSIS just doesn't even seem to function
> though.
You mean through the Wizard? Because you don't use SSIS the connect way, and the correct way isn't
exposed through the wizard...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188479488.270829.77320@.q4g2000prc.googlegroups.com...
> Weird - I think it is something having to do with either the browser
> or this forum, most likely related to character sets. When I copied
> and pasted the link into my browser, it came up as a bad link, but
> when I searched for the title of the document, I got it and it came up
> with the same URL you posted. Here it is:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/ececa4c4-1880-4d57-
> ac5a-00c4ea92a632.htm
> I did notice when I copied and pasted the URL, it had a sequence of 3
> dashes in a row in it, which isn't in the one you posted so it appears
> that the copy on the clipboard was being converted somehow.
> Anyway, I will take a look at the article to learn about this new
> method of doing deployment. Thanks for the assistance with this.
> I would still like to know why SSIS just doesn't even seem to function
> though.
>
> On Aug 30, 3:51 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Strange...
>> Here's the Internet/MSDN URL:http://msdn2.microsoft.com/en-us/library/ms141823.aspx
>> Can you navigate to the same topic in your local SQL Server BOL and post the URL you have there?
>> I'm
>> worried if there are two versions of BOL out there with different URL structures...
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>