Copying entire DB (including relationships) to remote server

Attempting to copy an entire DB from one server to another using advice from: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21859186.html

Generated scripts on the source db, ran scripts on the destination DB which recreated the schema.  While importing the table data with Allow Identity Insert option on, data import fails due to foreign key constraints.

This makes sense as the data must have to be imported in a specific order in order honor the key constrains.  Can't determine a method for accomplishing this.

svesceAsked:
Who is Participating?
 
sunezapaConnect With a Mentor Commented:
1. I did succesfully ask the supporters of the shared hosts to create the backupfile (in install it too). For free. Give it a try, maybe...

3. General: http://msdn.microsoft.com/en-us/library/ms177463.aspx
Details: http://msdn.microsoft.com/en-us/library/ms190273.aspx

code example:
 -- Disable the constraint
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;

-- Re-enable the constraint.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
 

Open in new window

0
 
sunezapaCommented:
1. You DID consider just to make a backup to file, and restore on the other server?! Quick... Ofcourse iuf is compatible servers, etc. ;-)
2. Wondering why the script do not create the data in "correct" order. But if not - then you may have to rearrange the inserts to follow the necessary order... could be big/annoying job...
3... hmmmm... turn off check of the "foreign key constrains" at import time...?
0
 
svesceAuthor Commented:
Thanks!

1.  Considered a backup and restore, but the db is on a shared host, so I don't have the required access
2.  I asked myself the same question and a manual reordering of the inserts would take prohibitively long time.
3. where is the check of the "foreign key constrains" at import time property set?

Sean
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
svesceAuthor Commented:
BTW -- the script runs fine -- the DB schema is recreated properly at the destination, but I'm using SQL Server Managers Import Task to import the data.    It appears to attempt import from an alphabetical list of tables with no regard to relationships.
0
 
sunezapaCommented:
3. or you could create the shemas WITHOUT all the contraints in the beginning,
and them add them later... after data-import...?
0
 
svesceAuthor Commented:
Thought of that too, but the auto generated script for the source schema relies almost exclusively on CREATE table.   Possible to just rename these to ALTER?
0
 
sunezapaCommented:
You can just change to ALTER, yes.
But I would just drop the whole database (takes a millisecond ;-) and use the CREATE script again...
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
My most expedient solution would consist in remedying Microsoft's failing by using EMS SQL Manager for SQL server to generate a data insertion script that takes dependencies into account ...
However, if you can't get your supervisor to shell out the $400 or so for the tool, one way would consist in writing a stored procedure that used sp_depends to list your database's tables in order of increasing number of dependencies. Thus, you would start with the tables having the most dependencies, those are likely to be your top level tables. Having ordered your list thus, manually generate a script for each set of data in the correct order.
Turning constraints on and off is done individually on each constraint and may take longer, depending on how many relationships you have.

Philippe
0
 
svesceAuthor Commented:
On second thought, that's not going to work..
0
 
svesceAuthor Commented:
Thanks for the great suggestions.   Convinced the host to do a backup and restore on their end.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.