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

views:

answers:

+1 Q:

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

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?

+2 A:

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.

Joe Stefanelli 2010-08-26 19:31:17

That's a great article. But not only because of growth but also a merge replication scenario where we need to have two separate databases to support both subscriber and publisher from the same database.

Jeff Widmer 2010-08-26 19:39:19

Not sure how your table count would break down (i.e., the 20 you want to move vs. how many total in the DB). The lesser of two evils might be to restore a backup of the database under the new name and drop the tables you don't want.

Joe Stefanelli 2010-08-26 19:42:03

@Joe: Yes, we have considered the backup/restore route but thought the move/copy with just the 20 tables would have been faster (less downtime for our customers) and a bit cleaner... but we may have to evaluate that as an answer. (Please add backup/restore as an answer so I can give you credit if we decide to go that route.)

Jeff Widmer 2010-08-26 19:46:39

I've edited my answer to include the backup/restore option.

Joe Stefanelli 2010-08-26 19:49:13

We are going to use the backup/restore route.

Jeff Widmer 2010-08-29 20:41:47

If you like/have SSIS you can explore using the Copy SQL Objects Task component to do this.

ajdams 2010-08-26 19:33:21

I do not have SSIS installed and also this is a 2005 database so I am not sure how good SSIS is with working on 2005 databases.

Jeff Widmer 2010-08-27 14:10:30

It would work just fine with 2005 but if you don't have it installed that won't do much good =P

ajdams 2010-08-27 15:23:31

SELECT * INTO new_table_name [IN new database] FROM old_tablename

Badkid 2010-08-27 04:11:44

That only will get the data. I want the indexes, keys, etc... everything.

Jeff Widmer 2010-08-27 11:33:15

I did also find this potential solution using SQL Server Management Studio. You can generate the scripts for the specific tables to move and then export the data using the Generate Scripts Wizard and Import/Export Wizard in SQL Server Management Studio. Then on the new database you would run the scripts to create all of the objects and then import the data. We are probably going to go with the backup/restore method as described in @Joe Stefanelli's answer but I did find this method and wanted to post it for others to see.

To generate the sql script for the objects:

SQL Server Management Studio > Databases > Database1 > Tasks > Generate Scripts...
The SQL Server Scripts Wizard will start and you can choose the objects and settings to export into scripts
- By default the scripting of Indexes and Triggers are not included so make sure to trun these on (and any others that you are interested in).

To export the data from the tables:

SQL Server Management Studio > Databases > Database1 > Tasks > Export Data...
Choose the source and destination databases
Select the tables to export
- Make sure to check the Identity Insert checkbox for each table so that new identities are not created.

Then create the new database, run the scripts to create all of the objects, and then import the data.

Jeff Widmer 2010-08-27 13:52:04

Try DBSourceTools.
http://dbsourcetools.codeplex.com.
This toolset uses SMO to script tables and data to disk, and also allows you to select which tables / views / Stored procedures to include.
When using a "deployment target", it will also automatically handle dependencies.
I have used it repeatedly for exactly this type of problem, and it's extremely simple and fast.

blorkfish 2010-09-06 07:54:11

ansaurus

tags:

views:

answers:

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

related questions