Database subsetting a viable method of slicing a database? Interested in getting a smaller copy of a database for testing purposes as restoring the full database takes hours, that would include all rows from tables referenced. Trying to do this manually would seem impossible.

I've seen a few tools out there that do this, but not sure which one would work with SQL Server? And perhaps I'm going about this all wrong and should be trying to do something different.

Any experience using data subsetting or anything else I should be doing instead?

Thanks for your help in advance.
Database subsetting is a great substitute for testing when you're dealing with a very large database or you want to limit the exposure of a production dataset. I would not recommend trying to do this manually, as you point out, unless you're dealing with a very simple schema (1-3 tables). Most commercial databases have tools for subsetting (e.g. Oracle does) although MS SQL Server surprisingly doesn't. There are a number of decent open source projects out there (e.g. Jailer, Condenser, RDBMS-Subsetter) that are good, but not quite out-of-the box.

A few additional caveats to keep in mind:


If you select too small of a subset, you may miss outliers and important edge cases. If you know of a particular set of records that you want in your test set, you should explicitly include these as part of subset.
You want to select an unbiased subset. Don't pick a geographic region or a fixed attribute to segment your subset population. I recommend you pick a primary table (e.g. users) and a randomly selected percentage of that table.
Scale testing does not always scale linearly. Don't assume that you can simply 100x the performance you measure on 1% of the original database. 


(note: I'm a contributor to an open source database subsetting tool, you can find out more about it here: https://www.tonic.ai/blog/condenser-a-database-subsetting-tool)