Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have five smallish databases all within a single SQL Server 2008 instance. The databases are related (by business entity but not by data) so I'd like to merge them into a single database and give each its own schema within that database.

Actually, I'd like the main database to keep the dbo schema. Originally I started by creating the schemas in the main database, then scripted the tables in the first database I want to move to create the structure, and then ran insert/select queries to migrate the data. I'm okay with this but it's time consuming and I haven't even gotten to the stored procedures yet. Plus one of the databases relies on the record IDs and simply doing an insert/select will throw them off.

Surely there's an easier way to do this.

I'm not a DBA but I'm comfortable working with queries and such in SSMS. I'm working on a dev server and I'm the only one who updates the data, so I can always restore from the production server if something goes wrong. Any pointers would be much appreciated.

share|improve this question

4 Answers 4

up vote 0 down vote accepted

I've found a solution and I'm going to post it here in case anyone else is looking for this in the future. I can't say it's the best way to do this, but it worked for me and was relatively painless. I'll also disclose up front that I created the new schemas in the primary database when I first asked this question (a week or two ago) so I honestly don't know if this step is necessary.

I repeated this procedure for each database.

  1. Created a unique schema name for the database.

  2. Ran the following query for each table in the database:

    ALTER SCHEMA [new_schema_name] TRANSFER [table_name];

  3. Used SSMS to generate CREATE scripts for all views, stored procedures, triggers and functions. (Fortunately I had few of these, so this went quickly.) I did have to append the schema name to all table refereces (e.g. [dbo].[table_name] changed to [new_schema_name].[table_name] but a search and replace made this fast and easy.

  4. Used the SQL Server Import and Export Wizard to export tables from the database to the primary database.

  5. Ran the CREATE scripts and deleted the old database.

share|improve this answer

As far as the ids, there is a statement to set the id. Reseed identity I believe. I second the redgate compare/ data cpare bundle - rocks.

I don't understand why you would do this though. This will make you backup and restore all objects at the same time. You're also going to have to refactor your app code as well. What benefits do you see from doing this?

share|improve this answer
    
The databases are for a series of personal web sites on my employer's server. It will make things easier for me to maintain. I originally had it set up like this a few years ago, then moved to a paid host that wouldn't allow the schemas so I had to split everything up. I would prefer being able to backup and restore everything in one shot. –  Michael Itzoe Nov 8 '09 at 2:48

Being that there's only one SQL Server instance, and assuming there's no overlap - you could use:

SELECT * FROM [OLD_DATABASE].dbo.[TABLE]
  INTO [NEW_DATABASE].dbo.[TABLE]

The table in the new database can't exist before you run the statement, but it will bring over the data.

share|improve this answer

Have a look at RedGate's SQL Compare. It can probably handle what you're looking for, or at least get you a lot closer. It's not free, but you can evaluate it to see if it'll work or not. IMHO it's well worth the $$ (as are all of their tools).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.