Modelling Database Creation Using the Model System Database in SQL Server

In this article, we will be discussing about the process of modelling database creation, using the Model system database in SQL Server. The Model database in SQL Server is used as the template for all the user databases that are created on a SQL Server instance. Most of the times, we do not modify the Model database, however there are cases where it might come in handy.

For example consider the following scenario: For audit purposes, you want to set your SQL Server instance, each time a user database is created, to automatically create an audit-related table as well as a relevant stored procedure that will allow each application that uses a database to be able to store in a table (in each database) the log-in and log-out times for each user.

To do this, we can create the relevant table and stored procedure in the Model database:

USE [model]
GO

CREATE TABLE [dbo].[AuditLog](
[userID] [int] NOT NULL,
[loginTime] [datetime] NOT NULL,
[logoutTime] [datetime] NOT NULL
) ON [PRIMARY]
GO


CREATE PROCEDURE dbo.logUser
  @userID int,
  @loginTime datetime,
  @logoutTime datetime
AS 
BEGIN
  INSERT INTO dbo.AuditLog
VALUES (@userID, @loginTime, @logoutTime)
END
GO

Let’s take a look at the Model database:

OK, so we have added the table and stored procedure in the Model database. Now let’s create three empty sample databases and check if the same tables and stored procedures are created automatically upon their creation:

CREATE DATABASE [SampleDB1];
GO
CREATE DATABASE [SampleDB2];
GO
CREATE DATABASE [SampleDB3];
GO

As you can see, all three databases were created fully based on the (modified) Model system database, thus the “AuditLog” table and “logUser” stored procedure were automatically created.

You can use the Model database for other things as well, as the above was just a simple example. However always have in mind when creating objects in the Model database that they will be automatically created for all user databases that will be created on that instance so be careful!

Learn More Tips like this – Enroll to the Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Learn More

Upgrade your Tech Skills – Learn all about Azure SQL Database

Enroll to our online course on Udemy titled “Introduction to Azure SQL Database for Beginners” and get lifetime access to high-quality lessons and hands-on guides about all aspects of Azure SQL Database.

Featured Online Courses:

• Introduction to Azure SQL Database for Beginners
• SQL Server 2019: What’s New – New and Enhanced Features
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Administration Tips
• Boost SQL Server Database Performance with In-Memory OLTP 
• Essential SQL Server Development Tips for SQL Developers
• Working with Python on Windows and SQL Server Databases
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
• Introduction to SQL Server Machine Learning Services
• Entity Framework: Getting Started – Complete Beginners Guide
• How to Import and Export Data in SQL Server Databases
• Learn How to Install and Start Using SQL Server in 30 Mins
• A Guide on How to Start and Monetize a Successful Blog

Read Also

• How to Patch a SQL Server Failover Cluster
• How to Patch a Standalone SQL Server Instance
• How to Add a Database to a SQL Server Availability Group Using T-SQL
• Top 10 SQL Server DBA Daily Tasks List
• The “Public” Database Role in SQL Server
• Encrypting a SQL Server Database Backup
• Learn Azure Data Lake Analytics by Example
• Azure Cosmos DB: Learn by Example
• How to Create an Azure SQL Server Virtual Machine
• How to Backup a SQL Server Database from On-Premises to Azure Storage
• … all our SQL Server Administration Articles

Check our Database Security and Administration Tool: DBA Security Advisor

DBA Security Advisor: Secure your SQL Server instances by scanning multiple instances against a rich set of security checks, and by getting recommendations and remediation steps.

Learn more

Rate this article: (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub