Solved

Copying entire DB (including relationships) to remote server

Posted on 2011-02-17
10
219 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 3

Accepted Solution

by:
sunezapa earned 500 total points
Comment Utility
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
Comment Utility
3. or you could create the shemas WITHOUT all the contraints in the beginning,
and them add them later... after data-import...?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:svesce
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
On second thought, that's not going to work..
0
 

Author Comment

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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now