Post reply

script out a database

  • September 7, 2011 at 11:36 am

    #256636

    Hello, I have a database I would like to be able to create quickly in a UAT setting.

    I would like to use the Tasks->Generate Scripts functionality within SQL Server 2008 to create 'schema only' for the database that I want to be able to regenerate at will. Here's are the options I set to generate a script:

    1. script entire database and all database objects

    2. save to new query window

    3. script triggers 'True'

    4. script indexes 'true'

    5. script unique keys 'true'

    6. script primary keys 'true'

    7. script foreign keys 'true'

    8. script check constraints 'true'

    types of data to script 'schema only'

    But when I execute my plan ie.

    1. delete db

    2. run the gen script i created

    I get errors.

    Msg 4060, Level 11, State 1, Line 65536

    Cannot open database "MGONextGen" requested by the login. The login failed.

    Msg 18456, Level 14, State 1, Line 65536

    Login failed for user 'REDTREE\hk'.

    the gen script starts with USE Master GO and CREATE DATABASE statements so I should not have to create the db. I am working on my local dev computer and am windows authenticated. I have admin privileges.

    Any ideas how I can get past this error to create a new database from the gen script?

  • September 7, 2011 at 11:38 am

    #1378176

    1. Do backup and restore.

    2. copy database

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • September 7, 2011 at 11:55 am

    #1378188

    Hi SQLFRNDZ

    This doesn't really clarify for me where running the gen script comes in. How will having a copy help me here? When I run the genscript, is it necessary to have a copy of the database I scripted out available?

  • September 7, 2011 at 12:26 pm

    #1378207

    In SSMS, right-click on the database, goto Tasks, then Generate Scripts 🙂

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • September 7, 2011 at 12:27 pm

    #1378210

    jared-709193 (9/7/2011)

    In SSMS, right-click on the database, goto Tasks, then Generate Scripts 🙂

    Thanks,

    Jared

    Sorry... I should learn to read everything first... Give me a minute or 2 and I will post a solution.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • September 7, 2011 at 12:31 pm

    #1378216

    So, my guess is that you are generating a user for the database in your scripts that does not exist on your local machine.

    Run each statement separately until you get to the one causing the error. Comment that one out, and go to the next one. Continue until you have these statements isolated. Let me know your results and we can move forward.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • September 7, 2011 at 12:53 pm

    #1378229

    OK, will do. Thanks Jared.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply