Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create a database from another database?

Tags:

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.

like image 795
FlySwat Avatar asked Aug 23 '08 19:08

FlySwat


People also ask

How do I create a database from an existing database?

In SQL Server Object Explorer, under the SQL Server node, expand your connected server instance. Right-click the Databases node and select Add New Database. Rename the new database to TradeDev. Right-click the Trade database in SQL Server Object Explorer, and select Schema Compare.

Can we create database inside database in SQL Server?

Thus, you can create a new database in SQL Server using T-SQL script or SSMS.


1 Answers

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.

like image 105
Fredrik Kalseth Avatar answered Sep 30 '22 17:09

Fredrik Kalseth