Create SQL Server Database With Pre-Defined Schema

In many situations, we need to create a database at runtime. A few years ago I worked on a project which was a multi tenant system where we create a new database for each tenant. For this task we followed a few steps like creatoing a blank database, executing schema script (tables, functions, stored procedures, views, triggers, etc), then executing master data. In this process there are 2 major issues we faced, one is it takes time to execute all this from application, as connection should be live while doing these steps, and exception handling and rollback. 

Basically, we need to understand how a database gets created. Whenever we create a new database in SQL Server it creates a copy of model database from system databases. It means that whatever there in model database will get replicated to the newly created database, like whole schema and data inside all tables.

To resolve this problem we have a solution which is very easy and less effort to fulfill this requirement. In SQL Server we have 4 default DBs inside System Databases. We need to use model DB for this. You can see this in below image,

We can add table schema, stored procedure, views, functions, and so on. I added one table and one stored procedure in model database as shown below,

Now when you create a new database whether it is from SSMS or from an application, it will replicate model db.

Now you can see model schema got replicated to newly created Database.

Here you can see one table with data, and one stored procedure got created. This is what I tried from SSMS, now we can check with C# code. please refer below,

And same DB got created as below,

This way we can use model database to generate pre-defined schema and data.

__Happy Coding__