current community

your communities

more stack exchange communities

Stack Exchange
tour help
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.
up vote 1 down vote favorite
1

I am looking to make a "schema only" (no data) copy of a database on my local server. I used Tasks -> Generate scripts to script the tables from my existing database and then after creating the new database opened a query window and pasted the copied scripts in.

When I ran the scripts I received the following error ...

Msg 2760, Level 16, State 1, Line 1
The specified schema name "abc" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Line 1
The specified schema name "xyz" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Line 1

Now some of the tables I selected to be scripted do make use of different schemas (abc & xyz) yet I am unsure as to why they cannot be scripted. Looking at my login I am mapped to the db_owner role which I would think would give me the appropriate permissions to create the schema. I am using SQL Server 2012.

share|improve this question
1  
Does the schema exists on the destination server ? –  Kin May 22 at 19:15
1  
Are you sure the script includes the CREATE SCHEMA definitions? If not, and the destination database is empty, how is abc going to already exist there? –  Aaron Bertrand♦ May 22 at 19:19
    
The schema does not already exists. It is a blank shell of a database. Doesn't the "generate scripts" functionality include the ddl to generate the schemas? –  webworm May 22 at 19:21
1  
@webworm did you look at the script you generated? Is it there? –  Aaron Bertrand♦ May 22 at 19:24
    
No it isn't. I just assumed (I know bad idea) that the ddl for generating the schema would be there. How does one generate schema via script. –  webworm May 22 at 19:25

1 Answer 1

up vote 4 down vote accepted

Do I understand correctly: you scripted tables only, created the database manually and then ran the script to create the tables?

You have to either script the whole database and create the new database using that script (change the DB name if you'll execute it on the same instance) or at least include schemas in the list of database objects to script ("Choose objects" step of the wizard).

enter image description here

share|improve this answer
1  
Ahaaa! That would be what I forgot. I shall slap myself with a trout several times. –  webworm May 22 at 19:29
    
+1 for mentioning the trout! @webworm –  Max Vernon May 23 at 0:26

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.