Data Migration

I have an interesting situation. We are attempting to standardize our data
models. Our database is large, with over 100 tables, and 50 Gigs of data.
Our differences between various clients is subtle, but again the volume of
tables makes it challenging.
What I am looking for is a tool that will migrate data from an older version
of a database to a top level version of the same database.
This is SQL Server 2000 SP 3a to SQL Server 2000 SP 3a.
Does anyone know of such a tool?
http://www.aspfaq.com/show.asp?id=2209
Andrew J. Kelly SQL MVP
"Michael @. SGMS" <MichaelSGMS@.discussions.microsoft.com> wrote in message
news:803B71E8-13B9-4F9B-889F-2A2DE65B7DA7@.microsoft.com...
>I have an interesting situation. We are attempting to standardize our data
> models. Our database is large, with over 100 tables, and 50 Gigs of data.
> Our differences between various clients is subtle, but again the volume of
> tables makes it challenging.
> What I am looking for is a tool that will migrate data from an older
> version
> of a database to a top level version of the same database.
> This is SQL Server 2000 SP 3a to SQL Server 2000 SP 3a.
> Does anyone know of such a tool?
|||This does not help. This only, unless I missed it, will give me the
difference in structure......What about moving the data?
"Andrew J. Kelly" wrote:

> http://www.aspfaq.com/show.asp?id=2209
>
> --
> Andrew J. Kelly SQL MVP
>
> "Michael @. SGMS" <MichaelSGMS@.discussions.microsoft.com> wrote in message
> news:803B71E8-13B9-4F9B-889F-2A2DE65B7DA7@.microsoft.com...
>
>
|||Michael @. SGMS wrote:
> This does not help. This only, unless I missed it, will give me the
> difference in structure......What about moving the data?
>
Couldn't you script these changes using T-SQL and modify as needed per
client? Another option is to use DTS.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||My first thought was to use a linked server (where the data I want to migrate
is) and then so something like:
Insert into newdb..TableA
Select * from olddb..TableA
BUT, there are contraints on the new database that cause these to fail. I
was hoping there was something out there that would script the dropping on
the indexes and keys on the new db, then the data move, then the creation of
the keys and constraints on the new db.
I already have a script that will make the old server structure like the
new...
"David Gugick" wrote:

> Michael @. SGMS wrote:
> Couldn't you script these changes using T-SQL and modify as needed per
> client? Another option is to use DTS.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||There isn't anything that I know of that will do all of that automatically.
The tool package from www.red-gate.com has a tool to compare the schema and
one to compare data. I know the schema tool will generate change scripts
but I don't know if it can generate data change scripts. That's usually
highly dependant on the schema and business rules.
Andrew J. Kelly SQL MVP
"Michael @. SGMS" <MichaelSGMS@.discussions.microsoft.com> wrote in message
news:82853B5B-571D-4F94-A967-CC0DD2BBA510@.microsoft.com...[vbcol=seagreen]
> My first thought was to use a linked server (where the data I want to
> migrate
> is) and then so something like:
> Insert into newdb..TableA
> Select * from olddb..TableA
> BUT, there are contraints on the new database that cause these to fail. I
> was hoping there was something out there that would script the dropping on
> the indexes and keys on the new db, then the data move, then the creation
> of
> the keys and constraints on the new db.
> I already have a script that will make the old server structure like the
> new...
> "David Gugick" wrote:
|||DB Ghost will do data and schema in a single process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Andrew J. Kelly" wrote:

> There isn't anything that I know of that will do all of that automatically.
> The tool package from www.red-gate.com has a tool to compare the schema and
> one to compare data. I know the schema tool will generate change scripts
> but I don't know if it can generate data change scripts. That's usually
> highly dependant on the schema and business rules.
> --
> Andrew J. Kelly SQL MVP
>
> "Michael @. SGMS" <MichaelSGMS@.discussions.microsoft.com> wrote in message
> news:82853B5B-571D-4F94-A967-CC0DD2BBA510@.microsoft.com...
>
>
|||Mark,
Will it generate a script to reconcile the data differences?
Andrew J. Kelly SQL MVP
"mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
news:69ED6392-ECEC-49B6-BEF3-C4A3C0E9E75A@.microsoft.com...[vbcol=seagreen]
> DB Ghost will do data and schema in a single process.
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Build, Comparison and Synchronization from Source Control = Database
> change
> management for SQL Server
>
>
> "Andrew J. Kelly" wrote:
|||yes.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Andrew J. Kelly" wrote:

> Mark,
> Will it generate a script to reconcile the data differences?
> --
> Andrew J. Kelly SQL MVP
>
> "mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
> news:69ED6392-ECEC-49B6-BEF3-C4A3C0E9E75A@.microsoft.com...
>
>
|||Check out http://www.agileinfollc.com DataStudio, it has a multiple thread
migration engine (20 thgreads) to migrate data.
"Michael @. SGMS" <MichaelSGMS@.discussions.microsoft.com> wrote in message
news:803B71E8-13B9-4F9B-889F-2A2DE65B7DA7@.microsoft.com...
>I have an interesting situation. We are attempting to standardize our data
> models. Our database is large, with over 100 tables, and 50 Gigs of data.
> Our differences between various clients is subtle, but again the volume of
> tables makes it challenging.
> What I am looking for is a tool that will migrate data from an older
> version
> of a database to a top level version of the same database.
> This is SQL Server 2000 SP 3a to SQL Server 2000 SP 3a.
> Does anyone know of such a tool?