Copy objects with DTS Import/Export Wizard?

Has the functionality of copying objects using the DTS Import/Export Wizard been removed in SQL Server 2005?

MSDN says this about the wizard:

"With the DTS Import/Export Wizard, you can transfer database objects such as indexes, views, roles, stored procedures, and referential integrity constraints. For more information, see Copy SQL Server Objects Task."

But the "for more imformation" link describes adding a task to a DTS package to copy an object, not how to use the wizard to copy objects.

It's been awhile since I've used SQL Server 2000, but as I recall it was possible to copy objects as well as data using the DTS Import/Export Wizard.

Thanks,

Ron

DTS has been enhanced to SSIS(Integration Services) in SQL 2005, so in this case you can use SSIS package to perform that copy SQL Server objects tasks which is similar to the copying the database objects.|||

You have a few options:

Copy Database Wizard:
Use this if you want to copy an entire database.

Right-click on a database in Management Studio, go to "Tasks", and choose "Copy Database...".|||

I also share in their frustrations... When you only have a few objects, in this case tables to transfer; you have very little options in the SQL2005 Import/Export wizard as compared to the SQL2000 DTS (Import/Export) wizard. Copy Database is out of the question for this scenario.

My problem lies in the fact that the SQL2005 Import/Export wizard does not create the indexes and keys when the table needs to be created in the target database, even if the source schema exists on the target database. Is there any way around this without pre-creating the target tables from scripts generated on the source DB? Seems that this manual step should be built into the wizard? Ay suggestions? We are running SP2 for SQL 2005.

There are suggestions that SSIS can be used, thats fine, but given the entirely different interface as compared to DTS, I don't have the time to deal with that when the wizard should be able to do this in a few minutes.

|||That option is fine for SQL2000/2005 to SQL2005, but is there any option to copy to SQL2000?|||I am getting very frustrated, for two days now I've been trying to work out how to copy a database (SQL express version) to my ISP which has the full MS 2005 server. I keep losing keys, stored procedures etc. When you right click on the database as you say and click on tasks there's no copy database there.|||Yey! now I get to spend 2 hours with ssis doing what used to take 2 minutes with dts import wizard.|||

I now use MS Database Publishing wizard which attaches to Database Explorer in Microsoft Developer Express. It works better than DTS Import/Export as I do not lose keys and can copy all object in the database. Great for making backups too.

Copy objects with DTS Import/Export Wizard?

Has the functionality of copying objects using the DTS Import/Export Wizard been removed in SQL Server 2005?

MSDN says this about the wizard:

"With the DTS Import/Export Wizard, you can transfer database objects such as indexes, views, roles, stored procedures, and referential integrity constraints. For more information, see Copy SQL Server Objects Task."

But the "for more imformation" link describes adding a task to a DTS package to copy an object, not how to use the wizard to copy objects.

It's been awhile since I've used SQL Server 2000, but as I recall it was possible to copy objects as well as data using the DTS Import/Export Wizard.

Thanks,

Ron

DTS has been enhanced to SSIS(Integration Services) in SQL 2005, so in this case you can use SSIS package to perform that copy SQL Server objects tasks which is similar to the copying the database objects.|||

You have a few options:

Copy Database Wizard:
Use this if you want to copy an entire database.

Right-click on a database in Management Studio, go to "Tasks", and choose "Copy Database...".|||

I also share in their frustrations... When you only have a few objects, in this case tables to transfer; you have very little options in the SQL2005 Import/Export wizard as compared to the SQL2000 DTS (Import/Export) wizard. Copy Database is out of the question for this scenario.

My problem lies in the fact that the SQL2005 Import/Export wizard does not create the indexes and keys when the table needs to be created in the target database, even if the source schema exists on the target database. Is there any way around this without pre-creating the target tables from scripts generated on the source DB? Seems that this manual step should be built into the wizard? Ay suggestions? We are running SP2 for SQL 2005.

There are suggestions that SSIS can be used, thats fine, but given the entirely different interface as compared to DTS, I don't have the time to deal with that when the wizard should be able to do this in a few minutes.

|||That option is fine for SQL2000/2005 to SQL2005, but is there any option to copy to SQL2000?|||I am getting very frustrated, for two days now I've been trying to work out how to copy a database (SQL express version) to my ISP which has the full MS 2005 server. I keep losing keys, stored procedures etc. When you right click on the database as you say and click on tasks there's no copy database there.|||Yey! now I get to spend 2 hours with ssis doing what used to take 2 minutes with dts import wizard.|||

I now use MS Database Publishing wizard which attaches to Database Explorer in Microsoft Developer Express. It works better than DTS Import/Export as I do not lose keys and can copy all object in the database. Great for making backups too.

sqlsql

Copy objects with DTS Import/Export Wizard?

Has the functionality of copying objects using the DTS Import/Export Wizard been removed in SQL Server 2005?

MSDN says this about the wizard:

"With the DTS Import/Export Wizard, you can transfer database objects such as indexes, views, roles, stored procedures, and referential integrity constraints. For more information, see Copy SQL Server Objects Task."

But the "for more imformation" link describes adding a task to a DTS package to copy an object, not how to use the wizard to copy objects.

It's been awhile since I've used SQL Server 2000, but as I recall it was possible to copy objects as well as data using the DTS Import/Export Wizard.

Thanks,

Ron

DTS has been enhanced to SSIS(Integration Services) in SQL 2005, so in this case you can use SSIS package to perform that copy SQL Server objects tasks which is similar to the copying the database objects.|||

You have a few options:

Copy Database Wizard:
Use this if you want to copy an entire database.

Right-click on a database in Management Studio, go to "Tasks", and choose "Copy Database...".|||

I also share in their frustrations... When you only have a few objects, in this case tables to transfer; you have very little options in the SQL2005 Import/Export wizard as compared to the SQL2000 DTS (Import/Export) wizard. Copy Database is out of the question for this scenario.

My problem lies in the fact that the SQL2005 Import/Export wizard does not create the indexes and keys when the table needs to be created in the target database, even if the source schema exists on the target database. Is there any way around this without pre-creating the target tables from scripts generated on the source DB? Seems that this manual step should be built into the wizard? Ay suggestions? We are running SP2 for SQL 2005.

There are suggestions that SSIS can be used, thats fine, but given the entirely different interface as compared to DTS, I don't have the time to deal with that when the wizard should be able to do this in a few minutes.

|||That option is fine for SQL2000/2005 to SQL2005, but is there any option to copy to SQL2000?|||I am getting very frustrated, for two days now I've been trying to work out how to copy a database (SQL express version) to my ISP which has the full MS 2005 server. I keep losing keys, stored procedures etc. When you right click on the database as you say and click on tasks there's no copy database there.|||Yey! now I get to spend 2 hours with ssis doing what used to take 2 minutes with dts import wizard.|||

I now use MS Database Publishing wizard which attaches to Database Explorer in Microsoft Developer Express. It works better than DTS Import/Export as I do not lose keys and can copy all object in the database. Great for making backups too.

Copy objects with DTS Import/Export Wizard?

Has the functionality of copying objects using the DTS Import/Export Wizard been removed in SQL Server 2005?

MSDN says this about the wizard:

"With the DTS Import/Export Wizard, you can transfer database objects such as indexes, views, roles, stored procedures, and referential integrity constraints. For more information, see Copy SQL Server Objects Task."

But the "for more imformation" link describes adding a task to a DTS package to copy an object, not how to use the wizard to copy objects.

It's been awhile since I've used SQL Server 2000, but as I recall it was possible to copy objects as well as data using the DTS Import/Export Wizard.

Thanks,

Ron

DTS has been enhanced to SSIS(Integration Services) in SQL 2005, so in this case you can use SSIS package to perform that copy SQL Server objects tasks which is similar to the copying the database objects.|||

You have a few options:

Copy Database Wizard:
Use this if you want to copy an entire database.

Right-click on a database in Management Studio, go to "Tasks", and choose "Copy Database...".|||

I also share in their frustrations... When you only have a few objects, in this case tables to transfer; you have very little options in the SQL2005 Import/Export wizard as compared to the SQL2000 DTS (Import/Export) wizard. Copy Database is out of the question for this scenario.

My problem lies in the fact that the SQL2005 Import/Export wizard does not create the indexes and keys when the table needs to be created in the target database, even if the source schema exists on the target database. Is there any way around this without pre-creating the target tables from scripts generated on the source DB? Seems that this manual step should be built into the wizard? Ay suggestions? We are running SP2 for SQL 2005.

There are suggestions that SSIS can be used, thats fine, but given the entirely different interface as compared to DTS, I don't have the time to deal with that when the wizard should be able to do this in a few minutes.

|||That option is fine for SQL2000/2005 to SQL2005, but is there any option to copy to SQL2000?|||I am getting very frustrated, for two days now I've been trying to work out how to copy a database (SQL express version) to my ISP which has the full MS 2005 server. I keep losing keys, stored procedures etc. When you right click on the database as you say and click on tasks there's no copy database there.|||Yey! now I get to spend 2 hours with ssis doing what used to take 2 minutes with dts import wizard.|||

I now use MS Database Publishing wizard which attaches to Database Explorer in Microsoft Developer Express. It works better than DTS Import/Export as I do not lose keys and can copy all object in the database. Great for making backups too.

Copy Logins from Server1 to Server2

Hello,
I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
there a way that I can export the logins acount info to the other sqlServer?
Thanks
Jose.DTS let you copy logins.
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:%23aETDE52DHA.3416@.tk2msftngp13.phx.gbl...

quote:

---

> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other

---

sqlServer?

quote:

---

> Thanks
> Jose.
>

---

|||There is a really good doc under Books On line, search for Log shipping...
Moving logins from one server to another is part of the failover process for
log shipping so the process is really documented well...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:#aETDE52DHA.3416@.tk2msftngp13.phx.gbl...

quote:

---

> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other

---

sqlServer?

quote:

---

> Thanks
> Jose.
>

---

Copy Logins from Server1 to Server2

Hello,
I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
there a way that I can export the logins acount info to the other sqlServer?
Thanks
Jose.DTS let you copy logins.
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:%23aETDE52DHA.3416@.tk2msftngp13.phx.gbl...
> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other
sqlServer?
> Thanks
> Jose.
>|||There is a really good doc under Books On line, search for Log shipping...
Moving logins from one server to another is part of the failover process for
log shipping so the process is really documented well...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:#aETDE52DHA.3416@.tk2msftngp13.phx.gbl...
> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other
sqlServer?
> Thanks
> Jose.
>

Copy db with dtswizard doesnt export triggers and stored procs

I am using dtswizard to export my db but it doesn't export triggers and all stored procedures. Am I doing anything wrong or is there a better way to do an exact copy of my DB? I am not able to use backup/restore because my db is on a server to which I don't have admin-right, and I have to pay my hoster to create a backup and email me.

TIA

Thomas

The SQL Server team have created a tool to move your database to a hosting server, try the link below for details. Post again if you still need help.

http://weblogs.asp.net/scottgu/archive/2007/01/11/tip-trick-how-to-upload-a-sql-file-to-a-hoster-and-execute-it-to-deploy-a-sql-database.aspx

|||

If you know IP or dns name of SQL server on your Host and you have user name and password to connect to your database I would recommend to script database to Query window next switch connection for this window to you host SQL server and just run it.

I did it for my web host SQL server and it worked perfectly.

If you can connect to your web SQL server from management studio it will pay in the future because you can modify you database using Microsoft tools which is probably better than any asp.net page.

Thanks

|||Actually I am trying to export the db (incl. triggers and stored procs) FROM my webhoster. I don't have permission to script the db :-(|||

thomasabcd:

Actually I am trying to export the db (incl. triggers and stored procs) FROM my webhoster. I don't have permission to script the db :-(

If you can see the stored procs and triggers just right click copy all and paste in your Management Studio. Hope this helps.

|||

Isn't there a way to copy the entire db with tables, rows, stored procedures and triggers? It seems cumbersome to e.g. go to every table, open triggers, script each trigger instead of just doing a bulk copy of the entire DB.

tia

|||

If you have access to your MDF and LDF you can use the code in thread below just put the files in the data sub folder in Microsoft SQL Server folder in programs and modify the code below for your database and box. Hope this helps.

http://forums.asp.net/thread/977493.aspx

|||

OR you could open up your query analyser ,set the output to text and then run this:

declare @.objNamesysnamedeclare objCurcursor for select [name]fromsysobjectswhere xtypein ('P','TR')OPEN objCurwhile 1=1begin-- Fetch datafetch objCurinto @.objNameif@.@.fetch_status <> 0break;execsp_helptext @.objNameENDCLOSE objCurDEALLOCATE objCur

The output then contains all the stored procs xtype = P and triggers xtype='TR'.Each objects script will be separated by the text, but I'm sure you can deal with that.

|||

Sounds like you have very limited permission on the hosted database. The best bet is to do just copy and paste all the stored proc and views.

If the data is important, i would pay your hoster for a backup or mdf of your database.

Most hosters nowaday offers free database backup.

Copy All tables

Hey I'm trying to simply copy all tables from one database to another on a
nightly basis.
If i select the database, choose export, and go through all the steps,
choosing the starting database, the destination database, the values to back
up and scheduling it, it all seems fine. But the job always fails. If i
select only a couple of tables, the job runs fine? anyone have any ideas
why this would be? Or advice on another way to backup the tables nightly?
Thanks
James,
I'm not sure if you're using replication or DTS for this but either way it
sounds like a locking issue. You may need to prevent users accessing the
tables when you do the copy eg setting the database to single user mode.
Alternatively, you could use transactional replication to synchronize the
data. The initial snapshot can be taken without requiring a table lock -
allowing concurrent access. This would perhaps be preferable as you won't be
duplicating data transfer each day.
Regards,
Paul Ibison
|||Are you deleteing all the rows in your destination table or appending? It sounds like you aren't. Go back in your package and delete the existing rows before appending.
Looking for a book on SQL Server replication
http://www.nwsu.com/0974973602.html
|||How do i lock the database?
This job is running at 3am, so there shouldn't be any users working, and the
backup seems to only run for about 5 minutes before failing?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u6twIeqNEHA.1456@.TK2MSFTNGP09.phx.gbl...
> James,
> I'm not sure if you're using replication or DTS for this but either way it
> sounds like a locking issue. You may need to prevent users accessing the
> tables when you do the copy eg setting the database to single user mode.
> Alternatively, you could use transactional replication to synchronize the
> data. The initial snapshot can be taken without requiring a table lock -
> allowing concurrent access. This would perhaps be preferable as you won't
be
> duplicating data transfer each day.
> Regards,
> Paul Ibison
>
|||James,
you can't lock a database, apart from making it single-user. I need to ask
some questions: Is your job runnung a dts package or replication or sps? Is
the structure of the tables changing each day or constant? How much of the
data is changing each day? How much data is there in the tables? What error
messages are returned? I need a bit more info before committing myself to
definite advice, although I suspect log-shipping or transactional
replication are what you need.
Regards,
Paul Ibison
|||Heres the error message:
Executed as user: CDC-EDB1\SYSTEM. ...Start: Copy SQL Server Objects
DTSRun OnProgress: Copy SQL Server Objects; Scripting objects for Transfer;
PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server
Objects; Scripting Logins; PercentComplete = 0; ProgressCount = 0 DTSRun
OnProgress: Copy SQL Server Objects; Scripting Users; PercentComplete = 0;
ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; Enumerating
objects for Transfer; PercentComplete = 0; ProgressCount = 0 DTSRun
OnProgress: Copy SQL Server Objects; Enumerating objects for Transfer : 0
Percent Completed; PercentComplete = 0; ProgressCount = 0 DTSRun
OnProgress: Copy SQL Server Objects; 0 Percent Completed; PercentComplete =
0; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects;
Enumerating objects for Transfer : 10 Percent Completed; PercentComplete =
10; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; 12
Percent Completed; Pe... Process Exit Code 1. The step failed.
Its a DTS package thats running. The values in the tables are changing
daily, I want them copied to another server where we run reports on the
data. This server is old and slow, so I don't want it slowing down the live
server.
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OaKmLUsNEHA.4060@.TK2MSFTNGP10.phx.gbl...
> James,
> you can't lock a database, apart from making it single-user. I need to ask
> some questions: Is your job runnung a dts package or replication or sps?
Is
> the structure of the tables changing each day or constant? How much of the
> data is changing each day? How much data is there in the tables? What
error
> messages are returned? I need a bit more info before committing myself to
> definite advice, although I suspect log-shipping or transactional
> replication are what you need.
> Regards,
> Paul Ibison
>
|||James,
the error message is not helpful and there is no way with the copy objects task of getting more info. It uses nolock , so locking is not the issue, and it is difficult to tell the exact cause of the problem. If you have constraints on the table and are us
ing row by row logging you can use the transform data task, which will give you a lot more info and possibly help you identify the cause of the problem. This will be more of a hit on your system though. For less of a hit, you can use log shipping, and the
n move the tables to a separate standby server and then copy them, or possibly overwrite the standby database completely if this suits your needs. Finally, there is transactional replication with a remote distributor which would also transfer the table da
ta.
Regards,
Paul Ibison