Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: How to create 100 databases???

    Hi,
    Here is my question. I need to build 100 databases for a class of 100 students.Now what I need to know is what is the best way to do it. Like in DB2 what I did was I created a single database and gave a schema to each user to work with. But I am not sure how to go about this in SQL server.

    I would really appreciate any kind of help in this regard.

    Thanks,
    Asha.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    You do not need to create 100 databases. You can simply create one database . Make 100 users and do not give them sa rights. Should work fine.
    Get yourself a copy of the The Holy Book

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

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    After you create the database in sql server (enterprise manager) -

    right click the database
    go to all tasks
    generate sql script

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't know...I'd think I'd have 1 created, dump it, and do:

    Code:
    
    
    DECLARE @cmd varchar(4000), @db_Name sysname, @i int
    
    SELECT @db_name = 'Brett', @i = 1
    
    WHILE @i < 101
    BEGIN
    -- Use to reset existing db's 
    --SELECT @cmd = 'ALTER DATABASE ' + @db_Name+'_'+CONVERT(varchar(3),@i)+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
    --EXEC(@cmd)
    
    
    
    SELECT @cmd = 'RESTORE DATABASE ' + @db_Name+'_'+CONVERT(varchar(3),@i)
       	+ '  FROM DISK = '+ '''' + 'D:\Tax\BackUp\TaxReconDB.dmp' + ''''
       	+ '  WITH  '
    	+ '  MOVE ' + '''' + 'TaxReconDB_Data' + '''' 
    	+ '  TO   ' + '''' + 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\' 
    	+ @db_Name+'_'+CONVERT(varchar(3),@i)+'.MDF' + ''''
            + ', MOVE ' + '''' + 'TaxReconDB_Log' + '''' 
    	+ '  TO   ' + '''' + 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\'
    	+ @db_Name+'_'+CONVERT(varchar(3),@i)+'.LDF' + ''''
    	+ ', REPLACE'
    
    SELECT @cmd
    EXEC(@cmd)
    
    SELECT @cmd = 'ALTER DATABASE ' + @db_Name+'_'+CONVERT(varchar(3),@i) + ' SET READ_WRITE'
    EXEC(@cmd)
    SELECT @cmd
    
    SELECT @i = @i + 1
    END
    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.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    brett ..dont you think it would be a better idea to create 100 users and give them access to the same database .. just my thoughts
    Get yourself a copy of the The Holy Book

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

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Enigma
    brett ..dont you think it would be a better idea to create 100 users and give them access to the same database .. just my thoughts
    Think about it...

    You have a class of 100 noob students...

    "hey, where'd my data go? Must have been a bug..."

    Can you imagine if you're pissed at someone, and their "final" is a mini system, and you placed some errant code in their sprocs to make them go booom?

    "Ya mean I was suppose to keep a script or dump (what's that again?) of the database?"

    F

    I think (well then again, maybe I don't, no matter) he's going for isolationizm...

    And I thought my method would be the laziest apporach...esp when resetting for the nect semster...

    Hey I can't even keep proffesional (did I really call them that) developers from clobbering each other...

    My Own Opinion (MOO)
    Last edited by Brett Kaiser; 11-13-03 at 16:28.
    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
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Enigma
    There are 10 types of people in this world. Those that understand binary & those that don't.
    Macka,

    That you?

    http://www.sqlteam.com/forums/pop_pr...isplay&id=1622
    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.

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Didnt get that ....
    Is somebody else using this line too ???
    Get yourself a copy of the The Holy Book

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

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Click on the link....

    If you think dbforums is cool....

    EDIT: You can't see his sig from there...but yeah it's the same....
    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.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Lazy is good, brett. I'd be even lazier. I'd put the script to create the database objects on a shared resource, and then make each student create their own database, log into it, and run the script. It can't hurt them to learn how to create a database and run a ddl script.

    ...and while they were doing it I'd go to the faculty lounge and have a cup of coffee and have my teacher's assistant answer their questions. That's just how lazy I would be.

    blindman

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Lazy is good, brett. I'd be even lazier. I'd put the script to create the database objects on a shared resource, and then make each student create their own database, log into it, and run the script. It can't hurt them to learn how to create a database and run a ddl script.

    ...and while they were doing it I'd go to the faculty lounge and have a cup of coffee and have my teacher's assistant answer their questions. That's just how lazy I would be.

    blindman
    You win!

    It takes a lot of brains to truly ascend to the higher art form of lazy...
    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
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you do not want to give them dbcreator rights on the server, you should be able to write a simple script. Put the students' names in a table with an identity column, then loop through the table making database names like JSMITHnnn where nnn is the identity value. I am afraid if you give the students the ability to size their own files, you might get a few surprises.


    do while not rs.eof
    qry = "create database " & name & id & " on primary..."
    conn.execute qry
    rs.movenext
    loop


    Oh, and remember to set model to truncate logs on checkpoint first. Save yourself many questions that way.

  13. #13
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Lazy is good, brett. I'd be even lazier. I'd put the script to create the database objects on a shared resource, and then make each student create their own database, log into it, and run the script. It can't hurt them to learn how to create a database and run a ddl script.

    ...and while they were doing it I'd go to the faculty lounge and have a cup of coffee and have my teacher's assistant answer their questions. That's just how lazy I would be.

    blindman
    Blindman ...
    I would have given you the certificate of being the laziest of all ... but i have a better Idea .. teach one student how to create a database and put him to the task of telling the rest of students .. no need for a teachers assistant too ...

    However , I stick to my idea of a single database , would be easier to handle than 100 odd databases ...

    moreover for 100 databases you would need 100 backup plans and all that ... just in case ...

    Keep one database, 100 users , dont give them dbo rights .. keep the users seperate .. no cross rights.
    Get yourself a copy of the The Holy Book

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

Posting Permissions

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