Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Goalie35
Yak Posting Veteran
81 Posts |
 Posted - 2013-05-10 : 00:19:45
|
| What's the best way to import data from an identical database?I have 2 identical databases: -MyDatabase_Prod-MyDatabase_DevMyDatabase_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
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-05-10 : 01:38:47
|
| 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 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-10 : 01:39:22
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2013-05-12 : 16:56:02
|
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 CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
Goalie35
Yak Posting Veteran
81 Posts |
Posted - 2013-05-14 : 00:09:56
|
| Thanks all. The backup/restore worked! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-14 : 00:44:11
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Best way to copy one database's data to another?