title: How to copy schema and some data from SQL Server to another instance?
body: <p>My product uses a SQL Server database - each client has their own deployed instance on their own intranet.  The db has about 200 tables.  Most of them are configuration tables that have only a few rows, but there are a couple of transaction data tables which may have several million rows.  Often I need to troubleshoot a customer's configuration problem so I need a copy of their database to work with locally on my dev system...  However it may be very large due to the transaction data, which makes it very difficult for the customer to send me a backup to work with.  I need a way to backup/copy/export everything EXCEPT for the large tables.</p>
  
  <p>Ideally, the large tables really belong a separate database catalog, such that a customer could backup their "data" separately from their "configuration".  However, I don't have the authority to make that type of change, so I'm looking for a good way to export or copy just the configuration parts without having to copy the whole thing.</p>
  
  <p>Currently I have a quick and dirty .NET app which uses a SQL Adapter and DataSet to blindly select * from all tables except the ones I want to omit, dump that to an XML file (this is my poor-man's export feature).  And I have companion app I can use locally to load the XML back into a DataSet and then use SQLBulkCopy to load it into my dev database.  I had to do some tricks to disable constraints and so forth, but I made it work.  This gets me the data, but not the schema.... ideally I'd like a way to get the schema along with it.  It is a brute force approach, and I believe there must be a simpler way.  Any suggestions for a better approach?</p>
  
  <p>The solution needs to be automated since I'm dealing with end-users who generally have very little IT support.</p>
  
  <p>(SQL 2005 and higher)</p>