July 24, 2008 at 7:11 am
I need to setup a production database with some data from a QA database.
What's the best way to transfer multiple tables of data between two databases and verify the results? The source tables have primary identity keys that must stay intact (i.e., primary keys do not start at 1 and have gaps). Additionally, data may be pulled from different database servers and total about 1-2 GB.
July 24, 2008 at 7:25 am
You can use DTS or Copy object wizard..
July 24, 2008 at 8:07 am
How do you access DTS and the "copy object wizard" in SQL Server 2005? Are you referring to the "SQL Server Import and Export Wizard"?
July 24, 2008 at 8:09 am
Is this something you will be doing once, or doing repeatedly?
If once, the Import/Export wizard will be fine. If repeatedly, then you'll need to either save the import package and re-run that, or build it another way.
Another key question is whether or not these databases are on the same server, the same LAN, or in different locations altogether. (Mainly important because of the latency and speed of connection question.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2008 at 8:12 am
Has the production database been setup yet? If not you can do a backup of the QA database and restore it on the production server and now it is the production DB. You can purge any unwanted data either before or after the move.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 24, 2008 at 9:07 am
GSquared,
On the production server, an older version of the database exists. For this next database version, the majority of existing tables will be truncated or removed. The rest of the existing production tables and data will be left intact. For tables with truncated data, the QA data will replace the existing production data.
Here's what I plan to do:
1. Phase 1 - create existing production copy (schema only) on qa database server; migrate necessary production data to QA database server; migrate QA data to "production-copy" database on qa server
2. Phase 2 - migrate QA data from QA server to production server
The QA and production servers are in completely different locations. The production database is approximately 1TB. However, I think only 1GB needs to be retained (i.e., copied from Production to QA for phase 1).
This particular upgrade will happen once. However, I expect to repeat this process (phase 1 and phase 2) approximately once a month.
Thanks again for your help,
Jon
July 24, 2008 at 9:10 am
Jack,
The database already exists in production. Even if it didn't exist, I'm not sure how feasible that option would be. I'm guessing the truncated database would be 1-2GB.
Thanks for your input,
Jon
July 24, 2008 at 9:29 am
You might want to take a look at ApexSQL Diff (www.apexsql.com), or the RedGate Compare and Data Compare products.
I use Apex's product for migrating structure, code and data between QA and Production. It's quite efficient at that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2008 at 10:00 am
GSquared,
Thanks for the info. I downloaded ApexSQL and used it to synchronize the databases. However, I ran into the following error:
Can't execute script against database. Exception of type 'System.OutOfMemoryException' was thrown.
The errored table contains 1 million, 600-byte maximum length rows. Any suggestions on how to get around this problem?
Thanks again,
Jon
July 24, 2008 at 12:23 pm
Apex Diff should give you a synchronization script. You're going to need to break it down into smaller chunks. Just run a few thousand rows at a time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply