Creating multiple instances of a generic database

Question

Hi all, currently I'm trying to have a setup where a generic database is distributed to students. They would develop an application using this database (Say a shopping cart application), submit their project onto our server, and then it would be graded automatically.

These databases are being run in Microsoft SQL Server 2005.

We're using user instances to instantiate each database, and multiple requests could be serviced at once. But, the problem is when more than one student submitted a project to be graded, the first database to be instantiated would be the only one and would overwrite all other copies that were currently open.

So if stu1 modified his database and stu2 and stu3 had their projects being graded concurrently, at the end of the grading stu1, stu2, and stu3 would have identical DB's at the end.

Is there any way I can have multiple independent copies of a generic database, each of which I can load concurrently and modify without having any changes made to any one affecting the others?

I did a little reading, and thought it might be possible to do something along the lines of:

1. Student submits project
2. Attach the database with unique db name (specified by student)
3. Do all necessary operations
4. Detach the database

I'm unsure if this would fix our problem or be possible, so any help would be much appreciated!

Answer 1

The simple solution would be to have them name their databases uniquely. Actually develop their code to use this unique name from the start. Can't you require the students to name their databases like STU_1234, where "1234" is their unique student number. You could go even further: STU_111_222_333_444444, where 111 is the class, 222 is the section, 333 is the assignment, and 444444 is their unique student id. This way there is no need to do anything fancy.

Answer 2

We do something similar where customers login to a website and enter setup information. The website then creates a copy of a generic database and loads the setup information into the database, creates a backup and sends the backup file to the customer. Sounds like your main issue is all the database are named the same. My suggestion is to use the following steps for grading:

1. Students submit a backup copy of their database (standard .BAK file).
2. Your grading system uses a script to first create a new blank database with a unique name on the server. It is important to create a new database first so that the files get created in the correct location on the server. To create a unique name you should use a project id, student id, and possibly a timestamp if students will be submitting multiple databases.
3. The script then replaces the blank database with the database from the backup using the With Replace option.
4. You are then able to run whatever grading steps are needed and either keep the database attached for future reference or drop it if space is limited.

You could write the script in either C# or Powershell, and use the following SQL queries:

USE Master
CREATE DATABASE [PROJECTID_STUDENTID_TIMESTAMP]
--Unique database is now created and stored in default SQL data/log file location
RESTORE DATABASE [PROJECTID_STUDENTID_TIMESTAMP] FROM DISK = 'c:\pathtofile\filename.bak' WITH REPLACE
--Student database is now available for grading
USE [PROJECTID_STUDENTID_TIMESTAMP]
--Add grading scripts here

Answer 3

I'm assuming that the student detaches the database and then you attach to the server. At that point, you can choose the database name to be unique.

Alternatively you can restore a backup and choose a name of the database at that time so that they are always unique.

In addition, assuming the students' work takes place entirely within the database, the name of the database should really be irrelevant to you and them for testing their code - except for the connection string for external access from any client code.