Database Subset challenge

Last Post 20 Mar 2008 05:58 PM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

20 Mar 2008 09:34 AM
The production database is very large and developers need only work with a subset of the database. I need to create an SSIS package that receives a parameter (s) to define the subset of data they wish to work with, and then the SSIS package will create a new database with a subset of data that can then be used locally.

1. The database schema must match that of the production system.
2. It is not an option to create an entire database and then delete data. The database subset will be created by a user running SSIS locally and most workstations and notebooks don't have room available for a complete db copy.

How I would think it should work:
1. Copy the production db into a new local db, omitting data. This gets me the latest schema, sprocs, triggers etc....
2. Load the 100+ tables. Most will be complete loads, but our main tables would only contain the subset based on the criteria passed into the package

Pretty simple, but I cannot seem to find any way in SSIS to copy or transfer a db without data. I don't want to have to script the db each time as this should be an automated process and storing a script means it will be out of data each time we do a release (every few weeks).

Any thoughts on this would be appreciated.

New Member
New Member

20 Mar 2008 05:58 PM
Here is what I suggest:

1. Backup production db
2. Restore it as StagingDB
3. Trim LARGE table
4. Backup StagingDB
5. Copy backup file to destination
6. Restore StagingDB backup as DevDB

You can do the login in SSIS easy.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC SSAS, SSIS, SSRS - April 21-24:

Acceptable Use Policy