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.

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

1
  • 1
    Ahaaa! That would be what I forgot. I shall slap myself with a trout several times. – webworm May 22 '14 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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