We help IT Professionals succeed at work.

Transfer SQL Server Objects Task & Constraints

anushahanna
anushahanna asked
on
613 Views
Last Modified: 2013-11-10
When you do a 'Transfer SQL Server Objects Task' for transfer of all data from all tables into another database in another server, will it check for all constraints if they are kept, before inserting the data? or will it try to insert in some order that it keeps?

thanks
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Instead you can use Copy Database Wizard or Backup / Restore and Detach / Attach approach to copy data from one database into another database directly right..

Author

Commented:
>>Copy Database Wizard

is that in SSIS?

I need to copy the data only and not the SPs involved, and hence thought about "Transfer SQL Server Objects Task". I had done before a database with less Constraints, but would like to clarify this before doing it on a bigger one..
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Copy Database Wizard is available in SSMS when you Right click a database..

>> I had done before a database with less Constraints, but would like to clarify this before doing it on a bigger one..

its better if you transfer data based upon the table dependencies by transferring Master tables first followed by child tables.

Author

Commented:
OK. I saw Copy Database Wizard. It is basically like backup & restore, right? it will not allow me to just copy data from tables, right?

>>its better if you transfer data based upon the table dependencies by transferring Master tables first followed by child tables.

but that will need too much manual intervention, though.,wouldn't, especially with many tables?
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Ok, I was mentioning with respect to Normal Backup and Restore and Copy Database Wizard approaches earlier.
And yes, 'Transfer SQL Server Objects Task' would look for dependencies internally.

Author

Commented:
Thanks for confirming that, Jegan.

Author

Commented:
Mark, in COPY Database wizard, I assume you would have me choose "Use the SQL Management Object Method"?

In the "select database objects", i do not see options for tables only, index only etc.

could you please confirm if i am in the right track.

The versions are 9.00.4053.00 on destination and 9.00.4273.00 on source.
Objects-in-Copy-wizard.bmp
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Oh, you are using copy database wizard.... For what ever dumb reason, I thought you were writing your own SSIS scripts. Sorry about that...

Well, no, there is not too much control, except that using copy database wizard is supposed to load in the correct sequence...

Author

Commented:
>>you need to select what options to copy with the COPY TABLE, like indexes, constraints etc....
in the above, what method were you envisioning?

The real goal here is to be able to just transfer the data into the destination database without touching/affecting other objects.

with Jamie's point #2, do you think this would work:

I will drop all constraints in the destination database.
then to simplify things, i could truncate/delete tables in destination to prepare it for the move.
then load all the data only using 'Transfer SQL Server Objects Task'
the data should load ok, since it is empty and no constraints either.
then i can apply the constraints..

do you think that should take care of it ok...

thanks for your feedback on this...
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
OK, copy database wizard is good if you want to copy the entire database lock stock and barrel.

I would normally right click on the database go into tasks and generate scripts.

you can select what objects to script, and I would normally do all indexes, foreign keys etc - it will do it in the right sequence.

Then you can apply that to your server and create the new database.

Then I would right click on the database again, and go into export data. It too will apply the data back in the correct sequence.

Author

Commented:
mark, OK. so copy DB option is not good for me in this case...

I went through your steps.. (create ALL scripts from DB1; create DB2 blank; run scripts in DB2; then export).. the error i am getting is .. can you see what i am not doing right?

- Execute the destructive SQL. (drop tables) (Success)

- Execute the constructive SQL. (create tables) (Success)

- Create a temporary table transfer package for [dbo].[sysdiagrams] (Stopped)

- Execute transfer package for [dbo].[sysdiagrams] (Stopped)

- Copying to [dbo].[sysdiagrams] (Stopped)

- Create a temporary table transfer package for [dbo].[tblPartTrack] (Stopped)

- Execute transfer package for [dbo].[tblPartTrack] (Stopped)

- Copying to [dbo].[tblPartTrack] (Stopped)

- Create a temporary table transfer package for [dbo].[tblPartTrack_Comm] (Stopped)

- Execute transfer package for [dbo].[tblPartTrack_Comm] (Stopped)

- Copying to [dbo].[tblPartTrack_Comm] (Stopped)
................ (for the rest of all the tables)..
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you for the direction.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.