One way to create a fresh copy of a database is to restore it from an initial backup copy. In other words
In Management Studio
- Create a new, empty database database
- Create the objects you want a new database to contain
- Create a full backup of the database, for more info, see
Create a Full Database Backup (SQL Server) - SQL Server | Microsoft Docs[
^]
Now you have an initial backup which you can use in your project
- Include the backup files in your project
- When needed use the RESTORE command to restore a new database from your backup, see
RESTORE (Transact-SQL) - SQL Server | Microsoft Docs[
^]
The approach above requires that you have administrative privileges to create a new database (obviously), but also the computer running the SQL server needs to have access to the files that are going to be restored. So you may need to copy the files to a proper location prior using them.
Another, quite similar approach would be to script the database instead of restoring a backup. In SSMS you can easily script the database, see
Tutorial: Script objects in SQL Server Management Studio - SQL Server | Microsoft Docs[
^]. Once you have the script you can execute the commands from the script as already described.
A short comment about this: Usually creating a database is not an operation normal users do. When the database is created one needs to know thing like
- The devices where the database files are going to be located
- What privileges should be granted to the database in context of the whole database server
- What kind of configuration the database server has, is it sufficient for the database created
- Any modifications needed for the server backup plans
etc.
So before creating new databases for the users, one should think if a single, shared database would be sufficient. Just pointing this out since this aspect wasn't touched in the question.