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.

Improve this question
5
  • 1
    Does the schema exists on the destination server ?
    – Kin Shah
    Commented May 22, 2014 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
    Commented May 22, 2014 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
    Commented May 22, 2014 at 19:21
  • 1
    @webworm did you look at the script you generated? Is it there?
    – Aaron Bertrand
    Commented May 22, 2014 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
    Commented May 22, 2014 at 19:25

1 Answer 1

Reset to default
4

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

Improve this answer
1
  • 1
    Ahaaa! That would be what I forgot. I shall slap myself with a trout several times.
    – webworm
    Commented May 22, 2014 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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