Solved

Copying entire DB (including relationships) to remote server

Posted on 2011-02-17
10
220 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
 
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
t-sql splitting string column 5 42
Merge Statement 3 39
Joining Two Tables In SQL and combining records 6 52
Solution for warm standby SQL server 20 34
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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short film showing how OnPage and Connectwise integration works.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 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

19 Experts available now in Live!

Get 1:1 Help Now