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.I use linked server for example. However , i tried to reproduce your problem
( I don't have right now access to the second server) so I tried to import
table from one database to another. When I cliked on Edit Mappings button
a 'Delete rows in destination table' was not enabled at all. So I went for
re-create destination table and it worked fine
What is the version are you using?
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188458357.350505.83230@.z24g2000prh.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:44 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> I use linked server for example. However , i tried to reproduce your problem
> ( I don't have right now access to the second server) so I tried to import
> table from one database to another. When I cliked on Edit Mappings button
> a 'Delete rows in destination table' was not enabled at all. So I went for
> re-create destination table and it worked fine
> What is the version are you using?
>
I have tried it on 2 different physical servers. One of them is using
version 9.0.3054 and the other is still on 9.0.1399 because I haven't
installed the service pack yet.
I am able to reproduce these results reliably (I have never been able
to get another result than this) in both environments. I also tried
dropping the table in the destination database and using the "create
table" option as you did, but the identity columns are always
completely regenerated instead of inserted as-is.|||Hi
I ran SQL Server Profiler while I use Import data wizard and I observered
that SQL Server used TRUNCATE TABLE when you specufy delete daat from
destination , so as you know it would delete/reset an identity on the table.
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188460907.491792.264000@.q3g2000prf.googlegroups.com...
> On Aug 30, 12:44 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> I use linked server for example. However , i tried to reproduce your
>> problem
>> ( I don't have right now access to the second server) so I tried to
>> import
>> table from one database to another. When I cliked on Edit Mappings
>> button
>> a 'Delete rows in destination table' was not enabled at all. So I went
>> for
>> re-create destination table and it worked fine
>> What is the version are you using?
> I have tried it on 2 different physical servers. One of them is using
> version 9.0.3054 and the other is still on 9.0.1399 because I haven't
> installed the service pack yet.
> I am able to reproduce these results reliably (I have never been able
> to get another result than this) in both environments. I also tried
> dropping the table in the destination database and using the "create
> table" option as you did, but the identity columns are always
> completely regenerated instead of inserted as-is.
>|||On Aug 30, 1:33 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> I ran SQL Server Profiler while I use Import data wizard and I observered
> that SQL Server used TRUNCATE TABLE when you specufy delete daat from
> destination , so as you know it would delete/reset an identity on the table.
> "NightOwl888" <sstorh...@.webuniverse.net> wrote in message
>
TRUNCATE TABLE is fine. The problem is that when the new data is
inserted, it renumbers all of the identities of the records. For
example, if the first record has an identity of 2, it renumbers it to
1, the second record that was 3 is now 2, and so on. This is a major
problem since this is the colunn all of the other foreign keys use. I
have tried changing drivers, and pretty much any combination of
actions you can think of to try to get this to work with SSIS, but no
luck.|||Hi
I have not seen in Profiler statement SET INSERT_IDENTITY ON for specific
table. So It could be bug as well.
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188468028.899752.220000@.x35g2000prf.googlegroups.com...
> On Aug 30, 1:33 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Hi
>> I ran SQL Server Profiler while I use Import data wizard and I
>> observered
>> that SQL Server used TRUNCATE TABLE when you specufy delete daat from
>> destination , so as you know it would delete/reset an identity on the
>> table.
>> "NightOwl888" <sstorh...@.webuniverse.net> wrote in message
> TRUNCATE TABLE is fine. The problem is that when the new data is
> inserted, it renumbers all of the identities of the records. For
> example, if the first record has an identity of 2, it renumbers it to
> 1, the second record that was 3 is now 2, and so on. This is a major
> problem since this is the colunn all of the other foreign keys use. I
> have tried changing drivers, and pretty much any combination of
> actions you can think of to try to get this to work with SSIS, but no
> luck.
>sql
Showing posts with label figure. Show all posts
Showing posts with label figure. Show all posts
Friday, March 23, 2012
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
>
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
>
Monday, March 19, 2012
Moving SQL Server 2005 x86 from Dell 1950 to Dell 2950 running SQL2005 x64
Hi,
I'm trying to figure out the best way to do this with the less
headache. I have a SQL 2005 server, let's call it SQL01. It is on a
Dell 1950. I want to move the SQL services to a Dell 2950 running 64
bit OS and SQL 2005 x64 but I want to keep the server name the same
and also retain login permissions on the databases that get moved.
I'm trying to see if I can leverage Backup Exec for this. So I would:
1. Perform a Full SQL Database backup on server SQL01 with Backup
Exec.
2. Remove Dell 1950 SQL01 server from Domain and reset computer
account in AD.
3. Join Dell 2950 to domain as SQL01 and Install SQL 2005 x64,
including any patches.
4. Perform SQL restore on new SQL01 server with Backup Exec
5. Bring up applications and test applications.
Is this reasonable or not?
Is there a better way?
On Apr 9, 4:57Xam, "John Bell" <jbellnewspo...@.hotmail.com> wrote:
> "Jose A. Hernandez" <jhbori...@.gmail.com> wrote in messagenews:b48aa821-eb8d-4b78-be58-cc20b7283745@.x41g2000hsb.googlegroups.com...
>
>
>
>
>
> Hi
> If you are using windows security and the access is granted by membership of
> domain groups your transfer of logins will be the simplest.
> http://support.microsoft.com/kb/314546is a good place to start. Don't
> forget jobs and SSIS packages if you have them.
> John- Hide quoted text -
> - Show quoted text -
Thanks for the KBs John, I'll check them up.
I'm trying to figure out the best way to do this with the less
headache. I have a SQL 2005 server, let's call it SQL01. It is on a
Dell 1950. I want to move the SQL services to a Dell 2950 running 64
bit OS and SQL 2005 x64 but I want to keep the server name the same
and also retain login permissions on the databases that get moved.
I'm trying to see if I can leverage Backup Exec for this. So I would:
1. Perform a Full SQL Database backup on server SQL01 with Backup
Exec.
2. Remove Dell 1950 SQL01 server from Domain and reset computer
account in AD.
3. Join Dell 2950 to domain as SQL01 and Install SQL 2005 x64,
including any patches.
4. Perform SQL restore on new SQL01 server with Backup Exec
5. Bring up applications and test applications.
Is this reasonable or not?
Is there a better way?
On Apr 9, 4:57Xam, "John Bell" <jbellnewspo...@.hotmail.com> wrote:
> "Jose A. Hernandez" <jhbori...@.gmail.com> wrote in messagenews:b48aa821-eb8d-4b78-be58-cc20b7283745@.x41g2000hsb.googlegroups.com...
>
>
>
>
>
> Hi
> If you are using windows security and the access is granted by membership of
> domain groups your transfer of logins will be the simplest.
> http://support.microsoft.com/kb/314546is a good place to start. Don't
> forget jobs and SSIS packages if you have them.
> John- Hide quoted text -
> - Show quoted text -
Thanks for the KBs John, I'll check them up.
Moving SQL Server 2005 x86 from Dell 1950 to Dell 2950 running SQL
Hi,
I'm trying to figure out the best way to do this with the less
headache. I have a SQL 2005 server, let's call it SQL01. It is on a
Dell 1950. I want to move the SQL services to a Dell 2950 running 64
bit OS and SQL 2005 x64 but I want to keep the server name the same
and also retain login permissions on the databases that get moved.
I'm trying to see if I can leverage Backup Exec for this. So I would:
1. Perform a Full SQL Database backup on server SQL01 with Backup
Exec.
2. Remove Dell 1950 SQL01 server from Domain and reset computer
account in AD.
3. Join Dell 2950 to domain as SQL01 and Install SQL 2005 x64,
including any patches.
4. Perform SQL restore on new SQL01 server with Backup Exec
5. Bring up applications and test applications.
Is this reasonable or not?
Is there a better way?"Jose A. Hernandez" <jhboricua@.gmail.com> wrote in message
news:b48aa821-eb8d-4b78-be58-cc20b7283745@.x41g2000hsb.googlegroups.com...
> Hi,
> I'm trying to figure out the best way to do this with the less
> headache. I have a SQL 2005 server, let's call it SQL01. It is on a
> Dell 1950. I want to move the SQL services to a Dell 2950 running 64
> bit OS and SQL 2005 x64 but I want to keep the server name the same
> and also retain login permissions on the databases that get moved.
> I'm trying to see if I can leverage Backup Exec for this. So I would:
> 1. Perform a Full SQL Database backup on server SQL01 with Backup
> Exec.
> 2. Remove Dell 1950 SQL01 server from Domain and reset computer
> account in AD.
> 3. Join Dell 2950 to domain as SQL01 and Install SQL 2005 x64,
> including any patches.
> 4. Perform SQL restore on new SQL01 server with Backup Exec
> 5. Bring up applications and test applications.
> Is this reasonable or not?
> Is there a better way?
Hi
If you are using windows security and the access is granted by membership of
domain groups your transfer of logins will be the simplest.
http://support.microsoft.com/kb/314546 is a good place to start. Don't
forget jobs and SSIS packages if you have them.
John|||On Apr 9, 4:57=A0am, "John Bell" <jbellnewspo...@.hotmail.com> wrote:
> "Jose A. Hernandez" <jhbori...@.gmail.com> wrote in messagenews:b48aa821-eb=8d-4b78-be58-cc20b7283745@.x41g2000hsb.googlegroups.com...
>
>
> > Hi,
> > I'm trying to figure out the best way to do this with the less
> > headache. =A0I have a SQL 2005 server, let's call it SQL01. =A0It is on =a
> > Dell 1950. =A0I want to move the SQL services to a Dell 2950 running 64
> > bit OS and SQL 2005 x64 but I want to keep the server name the same
> > and also retain login permissions on the databases that get moved.
> > I'm trying to see if I can leverage Backup Exec for this. =A0So I would:=
> > 1. Perform a Full SQL Database backup on server SQL01 with Backup
> > Exec.
> > 2. Remove Dell 1950 SQL01 server from Domain and reset computer
> > account in AD.
> > 3. Join Dell 2950 to domain as SQL01 and Install SQL 2005 x64,
> > including any patches.
> > 4. Perform SQL restore on new SQL01 server with Backup Exec
> > 5. Bring up applications and test applications.
> > Is this reasonable or not?
> > Is there a better way?
> Hi
> If you are using windows security and the access is granted by membership =of
> domain groups your transfer of logins will be the simplest.
> http://support.microsoft.com/kb/314546is a good place to start. Don't
> forget jobs and SSIS packages if you have them.
> John- Hide quoted text -
> - Show quoted text -
Thanks for the KBs John, I'll check them up.
I'm trying to figure out the best way to do this with the less
headache. I have a SQL 2005 server, let's call it SQL01. It is on a
Dell 1950. I want to move the SQL services to a Dell 2950 running 64
bit OS and SQL 2005 x64 but I want to keep the server name the same
and also retain login permissions on the databases that get moved.
I'm trying to see if I can leverage Backup Exec for this. So I would:
1. Perform a Full SQL Database backup on server SQL01 with Backup
Exec.
2. Remove Dell 1950 SQL01 server from Domain and reset computer
account in AD.
3. Join Dell 2950 to domain as SQL01 and Install SQL 2005 x64,
including any patches.
4. Perform SQL restore on new SQL01 server with Backup Exec
5. Bring up applications and test applications.
Is this reasonable or not?
Is there a better way?"Jose A. Hernandez" <jhboricua@.gmail.com> wrote in message
news:b48aa821-eb8d-4b78-be58-cc20b7283745@.x41g2000hsb.googlegroups.com...
> Hi,
> I'm trying to figure out the best way to do this with the less
> headache. I have a SQL 2005 server, let's call it SQL01. It is on a
> Dell 1950. I want to move the SQL services to a Dell 2950 running 64
> bit OS and SQL 2005 x64 but I want to keep the server name the same
> and also retain login permissions on the databases that get moved.
> I'm trying to see if I can leverage Backup Exec for this. So I would:
> 1. Perform a Full SQL Database backup on server SQL01 with Backup
> Exec.
> 2. Remove Dell 1950 SQL01 server from Domain and reset computer
> account in AD.
> 3. Join Dell 2950 to domain as SQL01 and Install SQL 2005 x64,
> including any patches.
> 4. Perform SQL restore on new SQL01 server with Backup Exec
> 5. Bring up applications and test applications.
> Is this reasonable or not?
> Is there a better way?
Hi
If you are using windows security and the access is granted by membership of
domain groups your transfer of logins will be the simplest.
http://support.microsoft.com/kb/314546 is a good place to start. Don't
forget jobs and SSIS packages if you have them.
John|||On Apr 9, 4:57=A0am, "John Bell" <jbellnewspo...@.hotmail.com> wrote:
> "Jose A. Hernandez" <jhbori...@.gmail.com> wrote in messagenews:b48aa821-eb=8d-4b78-be58-cc20b7283745@.x41g2000hsb.googlegroups.com...
>
>
> > Hi,
> > I'm trying to figure out the best way to do this with the less
> > headache. =A0I have a SQL 2005 server, let's call it SQL01. =A0It is on =a
> > Dell 1950. =A0I want to move the SQL services to a Dell 2950 running 64
> > bit OS and SQL 2005 x64 but I want to keep the server name the same
> > and also retain login permissions on the databases that get moved.
> > I'm trying to see if I can leverage Backup Exec for this. =A0So I would:=
> > 1. Perform a Full SQL Database backup on server SQL01 with Backup
> > Exec.
> > 2. Remove Dell 1950 SQL01 server from Domain and reset computer
> > account in AD.
> > 3. Join Dell 2950 to domain as SQL01 and Install SQL 2005 x64,
> > including any patches.
> > 4. Perform SQL restore on new SQL01 server with Backup Exec
> > 5. Bring up applications and test applications.
> > Is this reasonable or not?
> > Is there a better way?
> Hi
> If you are using windows security and the access is granted by membership =of
> domain groups your transfer of logins will be the simplest.
> http://support.microsoft.com/kb/314546is a good place to start. Don't
> forget jobs and SSIS packages if you have them.
> John- Hide quoted text -
> - Show quoted text -
Thanks for the KBs John, I'll check them up.
Monday, March 12, 2012
moving sql database
What is the procedure for moving a database from one c:\
to D:\ on the same computer.
I figure I have to use sql analyzer but once there what
are the steps.
Thanks
You could refer to this article on Microsoft's website.
http://support.microsoft.com/?kbid=224071
This should get you started.
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.
to D:\ on the same computer.
I figure I have to use sql analyzer but once there what
are the steps.
Thanks
You could refer to this article on Microsoft's website.
http://support.microsoft.com/?kbid=224071
This should get you started.
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.
moving sql database
What is the procedure for moving a database from one c:\
to D:\ on the same computer.
I figure I have to use sql analyzer but once there what
are the steps.
ThanksYou could refer to this article on Microsoft's website.
http://support.microsoft.com/?kbid=224071
This should get you started.
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.
to D:\ on the same computer.
I figure I have to use sql analyzer but once there what
are the steps.
ThanksYou could refer to this article on Microsoft's website.
http://support.microsoft.com/?kbid=224071
This should get you started.
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)