Solved

Copying entire DB (including relationships) to remote server

Posted on 2011-02-17
10
221 Views
Last Modified: 2012-05-11
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.

0
Comment
Question by:svesce
  • 5
  • 4
10 Comments
 
LVL 3

Expert Comment

by:sunezapa
ID: 34922256
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
 

Author Comment

by:svesce
ID: 34922302
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
 

Author Comment

by:svesce
ID: 34922331
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 3

Accepted Solution

by:
sunezapa earned 500 total points
ID: 34922352
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
 
LVL 3

Expert Comment

by:sunezapa
ID: 34922365
3. or you could create the shemas WITHOUT all the contraints in the beginning,
and them add them later... after data-import...?
0
 

Author Comment

by:svesce
ID: 34922379
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
 
LVL 3

Expert Comment

by:sunezapa
ID: 34922412
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
 
LVL 9

Expert Comment

by:damerval
ID: 34922414
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
 

Author Comment

by:svesce
ID: 34922416
On second thought, that's not going to work..
0
 

Author Comment

by:svesce
ID: 34922577
Thanks for the great suggestions.   Convinced the host to do a backup and restore on their end.  
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question