How to copy schema and some data from SQL Server to another instance?

Question

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.

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.

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?

The solution needs to be automated since I'm dealing with end-users who generally have very little IT support.

(SQL 2005 and higher)

Answer 1

Could you generate the scripts from SSMS directly?

1. Right click the database
2. Select Tasks -> Generate Scripts
3. (Click next if you get the intro screen)
4. Select "Select specific database objects"
5. Pick the objects to generate scripts for (tables, stored procedures, etc...)
6. Click Next, then specify the output filename
7. Click Finish to generate the script

This will generate the schemas only. If you want to do data generating scripts as well, in step 6) click the Advanced button and scroll down to the "Types of data to script" and change it from "Schema only" to "Data only" or "Schema and data"

In your situation, you could do "Schema and data" for all of your small config tables, then do "Schema only" for the large tables where you don't want to export the data.

I know this isn't totally automated however it's pretty close. If you want to look at automating this even further, check out this thread. Not sure if this works on SQL 2005:

How can I automate the "generate scripts" task in SQL Server Management Studio 2008?