Sql-server – Merge mulitple databases into single database with multiple schemas in SQL Server 2008

sql serversql-server-2008ssms

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.

Best Answer

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.

Related Topic