Create a database from another database?

Question

Is there an automatic way in SQL Server 2005 to create a database from several tables in another database? I need to work on a project and I only need a few tables to run it locally, and I don't want to make a backup of a 50 gig DB.

UPDATE

I tried the Tasks -> Export Data in Management studio, and while it created a new sub database with the tables I wanted, it did not copy over any table metadata, ie...no PK/FK constraints and no Identity data (Even with Preserve Identity checked).

I obviously need these for it to work, so I'm open to other suggestions. I'll try that database publishing tool.

I don't have Integration Services available, and the two SQL Servers cannot directly connect to each other, so those are out.

Update of the Update

The Database Publishing Tool worked, the SQL it generated was slightly buggy, so a little hand editing was needed (Tried to reference nonexistent triggers), but once I did that I was good to go.

Answer 1

You can use the Database Publishing Wizard for this. It will let you select a set of tables with or without the data and export it into a .sql script file that you can then run against your other db to recreate the tables and/or the data.

Answer 2

Integration Services can help accomplish this task. This tool provids advanced data transformation capabilities so you will be able to get exact subset of data that you need from large database.

Assuming that such data is needed for testing/debugging you may consider applying Row Sampling to reduce amount of data exported.

Answer 3

Create your new database first. Then right-click on it and go to the Tasks sub-menu in the context menu. You should have some kind of import/export functionality in there. I can't remember exactly since I'm not at work right now! :)

From there, you will get to choose your origin and destination data sources and which tables you want to transfer. When you select your tables, click on the advanced (or options) button and select the check box called "preserve primary keys". Otherwise, new primary key values will be created for you.

Answer 4

I know this method can hardly be called automatic but why don't you use a few simple SELECT INTO statements?

Because I'd have to reconstruct the schema, constraints and indexes first. Thats the part I want to automate...Getting the data is the easy part.

Thanks for your suggestions everyone, looks like this is easy.