Tool to create database subset?

Tool to create database subset?

Post by Darren Fulle » Wed, 15 Aug 2001 01:05:26



Our production databases are growing quite large (40-50Gb) and we would like
to create smaller duplicates for testing and development.  Does any body
know of a tool that can extract a subset of data from a database, while
maintaining referential integrity?

Thanks,

Darren Fuller

 
 
 

Tool to create database subset?

Post by Dan Guzma » Wed, 15 Aug 2001 21:47:01


We had a similar requirement and developed a solution using DTS with a VB
GUI front end.  The approach we took was to identify and develop extract
queries for the large tables based on user supplied criteria (e.g. customer
list, transaction date range).  To keep things simple (the application
consists of multiple databases and several hundred tables) smaller tables
are transferred in their entirety.  The process performs the following
steps:

1) create new target database
2) copy table structure only to target db (no constraints, indexes,
triggers)
3) copy data using selective extract queries
4) copy data for remaining tables
5) copy all other database objects (constraints, indexes, views, procs,
etc.)

The VB app creates the DTS package and transformations on the fly.  The
process is actually pretty simple since the source and target schema are
identical.  The hard part was developing the selective extract queries.
This required a lot of effort for us because of our complex data model and
some data issues.

Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------


Quote:> Our production databases are growing quite large (40-50Gb) and we would
like
> to create smaller duplicates for testing and development.  Does any body
> know of a tool that can extract a subset of data from a database, while
> maintaining referential integrity?

> Thanks,

> Darren Fuller


 
 
 

1. Tool to create database subset?

Our production databases are growing quite large (40-50Gb) and we would like
to create smaller duplicates for testing and development.  Does any body
know of a tool that can extract a subset of data from a database, while
maintaining referential integrity?

Thanks,

Darren Fuller

2. Request for copy of Charter

3. Creating a subset database file

4. ISP with Oracle?

5. Create and Sychronize subsets of your Oracle Database

6. virtual cube with identical dimensions

7. Calculated subtotal of a subset of a subset in a file

8. D1 Newbie: Little Question on DB Form

9. Creating Multiple Subsets Using Replication

10. Creating a data subset in a grid

11. Creating subset in same file

12. HELP: Creating your own Visual Database Tools