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.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.|||After you create the database in sql server (enterprise manager) -
right click the database
go to all tasks
generate sql script|||I don't know...I'd think I'd have 1 created, dump it, and do:
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\Dat a\'
+ @.db_Name+'_'+CONVERT(varchar(3),@.i)+'.MDF' + ''''
+ ', MOVE ' + '''' + 'TaxReconDB_Log' + ''''
+ ' TO ' + '''' + 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\'
+ @.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 ..dont you think it would be a better idea to create 100 users and give them access to the same database .. just my thoughts|||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)|||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_profile.asp?mode=display&id=1622|||Didnt get that ...
Is somebody else using this line too ?|||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...|||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|||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...|||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.|||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.
