How to move tables from one sql server database to another?

Question

We have a database that has grown to about 50GB and we want to pull out a certain set of tables (about 20 of them) from within that database and move them into a new database. All of this would be on the same SQL Server. The tables that we want to pull out are about 12GB of space (6GB data, 6GB indexes).

How can we move the tables from one database to the second but make sure the tables that are created in the new database are an exact copy of the originals (indexes, keys, etc.)? Ideally I want a copy/paste from within SQL Server Management Studio but I know this does not exist, so what are my options?

Answer 1

To do this really easily with SQL Server 2008 Management Studio:

1.) Right click on the database (not the table) and select Tasks -> Generate Scripts

2.) Click Next on the first page

3.) If you want to copy the whole database, just click next. If you want to copy specific tables, click on "Select Specific Database Objects", select the tables you want, and then click next.

4.) Select "Save to Clipboard" or "Save to File". IMPORTANT: Click the Advanced button next to "Save to File", find "Types of data to script", and change "Schema only" to "Schema and data" (if you want to create the table) or "Data only" (if you're copying data to an existing table). This is also where you'd set other options such as exactly what keys to copy, etc.

5.) Click through the rest and you're done!

Answer 2

If you're moving the tables to a whole new database just because of growth, you might be better off considering using filegroups in your existing database instead. There will be a lot fewer headaches going forward than trying to deal with two separate databases.

EDIT

As I mentioned in my comments below, if you truly need a new database, depending on the total number of tables involved, it might be easier to restore a backup of the database under the new name and drop the tables you don't want.