[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Copying entire DB (including relationships) to remote server

Posted on 2011-02-17
10
Medium Priority
?
232 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 3

Accepted Solution

by:
sunezapa earned 2000 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:Philippe 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

640 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