SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Best way to copy one database's data to another?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Goalie35
Yak Posting Veteran

78 Posts

Posted - 05/10/2013 :  00:19:45  Show Profile  Reply with Quote
What's the best way to import data from an identical database?

I have 2 identical databases:
-MyDatabase_Prod
-MyDatabase_Dev

MyDatabase_Dev, is an exact copy of Prod, which I setup a few months back. Now however, I want to update this db with the latest version of prod, but I'm receiving a sql server error.

To update, I'm going to my dev database, clicking "import data", selecting MyDatabase_Prod as the source, checking the box to delete all rows currently in MyDatabase_Dev before updating, and finally, running it. I'm receiving an error though, saying there's an issue with references to foreign keys.

What's the best way to update my dev database, without getting the foreign key errors?

thanks

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2019 Posts

Posted - 05/10/2013 :  01:38:47  Show Profile  Visit jackv's Homepage  Reply with Quote
If it's a complete copy - have you thought about maybe BACKUP the prod , RESTORE over the dev?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/10/2013 :  01:39:22  Show Profile  Reply with Quote
why not backup and restore as a new copy from prod database and drop existing dev? Of course, you've to take care of difference in permissions,principals etc by scripting the existing dev permissions and applying it after the restore

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/12/2013 :  16:56:02  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
backup / restore is the probably the best way.

If not then:
Redgate have a tool called SQL Data Compare which I've used in the past to compare production database. It's pretty good -- think there is also a free trial -- try googling for it.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

Goalie35
Yak Posting Veteran

78 Posts

Posted - 05/14/2013 :  00:09:56  Show Profile  Reply with Quote
Thanks all. The backup/restore worked!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/14/2013 :  00:44:11  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000