Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2003
    Posts
    280

    Unanswered: How do I create a new database with an sql script?

    I would like to learn how to create a new database by running a sql script. Then afterwards, I will run the sql script to create new tables. I know how to run scripts to create table but not for a new database.

    What would be the query to create a new database? I will be using this sql script for my .NET application Setup file.

    Thanks,

    Don

  2. #2
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    CREATE DATABASE

    Examples are on the bottom.
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  3. #3
    Join Date
    Apr 2003
    Posts
    280
    How would I implment this? This is the template from the Query Analyer. Basically, I want to check if the database exist. If it does, then skip it. If not, then create it.

    I have try it and I can't get it to work. I just change the word "database_name" to one of the database name in the SQL Server 2000.



    IF EXISTS (SELECT *
    FROM master..sysdatabases
    WHERE name = N'<database_name, sysname, test_db>')
    DROP DATABASE <database_name, sysname, test_db>
    GO

    CREATE DATABASE <database_name, sysname, test_db>
    GO

  4. #4
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    Did you look at the Examples at the bottom of the link?

    Code:
    IF EXISTS (SELECT * 
    FROM master..sysdatabases 
    WHERE name = N'TestDB')
    DROP DATABASE TestDB
    GO
    
    CREATE DATABASE TestDB
    GO
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  5. #5
    Join Date
    Apr 2003
    Posts
    280
    Yes, I have. Thank you! I'll ask you more question if I can't find my answer on there or need more explanation on it. Thanks again.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    my 2 cents

    This is a bad idea....but the that's the dba in me...besides I've seen a lot of bad ideas...

    MOO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by Brett Kaiser
    my 2 cents

    This is a bad idea....but the that's the dba in me...besides I've seen a lot of bad ideas...

    MOO

    what's bad about it? I've come across tons of installers that do this. It actually makes it much easier on the person installing the app. Just have them give the installer the servername and a user w/ privs to create a db, then it does everything else for you.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    why not just use osql
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by Brett Kaiser
    why not just use osql

    ah, ok. I misunderstood what you were uneasy about... I thought you meant building the DB on the fly.

    my mistake!

  10. #10
    Join Date
    Apr 2003
    Posts
    280
    Isn't osql, you have to use a command prompt? If so, I'm trying to find the easiest way to create a database because the users will be installing it.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No, You create a bat file that has a bunch of osql executions.

    Each one executes a segement of your DDL to build the database...

    Create the database might one

    A file of plain create tables might be another script

    A file to load tables before you create indexes might be another...ok, that would be bcp

    A file to create index...jobs, dts packages, whatever

    and a job to take a backup of the virgin database

    Is that what you are looking to do?

    The user then just executes the bat file

    and you a very easy repeatable process...

    Sounded like to me you wanted some gui to let the do all this
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah it's the .net that bugged me
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Apr 2003
    Posts
    280
    Quote Originally Posted by Brett Kaiser
    No, You create a bat file that has a bunch of osql executions.

    Each one executes a segement of your DDL to build the database...

    Create the database might one

    A file of plain create tables might be another script

    A file to load tables before you create indexes might be another...ok, that would be bcp

    A file to create index...jobs, dts packages, whatever

    and a job to take a backup of the virgin database

    Is that what you are looking to do?

    The user then just executes the bat file

    and you a very easy repeatable process...

    Sounded like to me you wanted some gui to let the do all this
    Do you have any example of creating a bat file, with the osql execution?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •