Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Lets assume for the sake of argument that I have the following tables in a single SQL Server database.

mySchema.Users
mySchema.Products
-- 
secondSchema.Users
secondSchema.Contracts
secondSchema.ContractItems
--
oldSchema.People
oldSchema.Table1
oldSchema.Table2
oldSchema.Table3
oldSchema.Table4
-- 
and so on and so forth

Each "schema" is fully independent of the others. The only reason they are in a single database, is due to hosting and cost limitations. In a perfect world these would have been in separate databases to begin with.

What things will I need to do when (and if ever) I need to move secondSchema into a new database, all by itself because its usage goes up and it now requires being housed in its own database.


I have reviewed Decision criteria on when to use a non-dbo schema vs a new Database, and as I've stated above, there is no relational or transactional justification for them being in the same database. They are in there for cost reasons.

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

You can script out that portion of the database and then run a CREATE DATABASE with the scripted schema and data from your original database.

When you Generate Scripts..., you will have the ability to select specific objects as opposed to the whole database. You can select the desired schema, and all of the objects that the schema owns. This will generate a script containing your schema and schema objects. A bit of a manual operation, but probably the easiest method.

In the generated script, you can alter the schema name to be what the new database's schema should be called, but realize that the object definitions also need to reflect the new schema's name.

share|improve this answer
    
You can also "script create database" when you do that ... –  jcolebrand Jun 14 '12 at 19:27
add comment

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.