Best way to create a sample database?

Question

Best way to create a sample database?

Laura Henderson

SSC Enthusiast

Points: 103
More actions
September 8, 2008 at 2:40 pm

#212407

I'm a rank novice when it comes to SQL Server & SQL scripts, so I apologize if I'm unclear about any of this.
We have a very large SQL database, lots of tables and lots of rows in most of the tables. We're considering moving to a new system and want to get an estimate of conversion costs from the vendor. The vendor has asked that I send the database to them so they can more accurately estimate the cost of conversion.
The database is too huge to send out for an estimate, so I want to create a sample. It should have all of the tables but just a subset of the rows in each table.
I've looked at DTS but can't see how to do it in DTS Designer. I've come across mention of DTS Query Designer, but haven't a clue how to find it. If I can find it, will it help?
I'm hoping there's some way to do this without having to touch each table in the database.
Any suggestions?
Thanks,
Laura

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

There isn't a good way to do this. You could script grabbing 10% of rows in all tables, but the issue is that you'll have FKs and child rows that might get lost.

for lookup tables, you probably don't care about removing data. For larger structures, you might take say 100 rows at the parent level and then delete any other children that don't match up and then remove other parents. That would get you a smaller database.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Laura

The vendor has asked that I send the database to them so they can more accurately estimate the cost of conversion.

Perhaps a dumb question on my part but are you sure the new vendor wants the database with data or just the database schema? If just the schema you could script it out for them and send the script file. Admittedly the scripting could be a somewhat lengthy task but doable.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Laura Henderson SSC Enthusiast Points: 103 More actions · Answer 3

Oh, I was afraid of this, Steve & bitbucket. So much for an easy way to make the data base!

You're right, bitbucket, the vendor might be able to get by with just the schema. As a novice, I've always found it easier to understand how things relate if I can get a look at the data. I'll check with the vendor before I spend too much more time on this.

Thanks,

Laura

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Before you send any data to the vendor, make sure you're familiar with any legal requirements in that area. I don't know what kind of data's in that table, but you should check that it's not something that will violate company rules if sent to someone outside.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Laura Henderson SSC Enthusiast Points: 103 More actions · Answer 5

GilaMonster (9/9/2008)
Before you send any data to the vendor, make sure you're familiar with any legal requirements in that area. I don't know what kind of data's in that table, but you should check that it's not something that will violate company rules if sent to someone outside.

This is something I do have to take into account and I haven't decided what to do about it yet. It may actually be easier than trying to give them some sample data!

Thanks,

Laura

Laura Henderson SSC Enthusiast Points: 103 More actions · Answer 6

I found a way to make a sample database that was kind of time-consuming, but which worked for my purposes. A poster at ITtoolbox.com suggested that I create a DTS package that copies all of the files to my sample db, then go into the package and add SELECT TOP 100 to the query for each table. I actually used SELECT TOP 10 PERCENT and only on the largest tables.

After that I zipped up the database and got it down to a size of 29 MB. I'm going to FTP it to the vendors.

I had to do some research to learn how to get a list of tables with their row counts and another list of tables and their columns. This site was really, really helpful for that.

Thanks, all!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

How big is your database backup file? Tell the vendor to send you a USB drive that is big enough to hold it. If necessary, you can zip the file to get it to fit.

Jason Crider SSCertifiable Points: 6019 More actions · Answer 8

Guess we better all be careful :

http://www.freepatentsonline.com/y2007/0118573.html

I'm trying to do this myself as well.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter