Model Database:-
1. Model database is a third-system database.
2. This database is used as a template to create a new user
database.
3. When we fire a command to create a new user database, the system just
creates the replica of the Model database with a specified name which includes predefined
objects, settings, and configurations.
4. Changes made in the Model database, will be inherited by all user databases. It means, that if we create an object in the Model database, it'll be included automatically in all newly created user databases.
5. We can't add any file or filegroup in the Model database.
6. We can't change the owner of the Model database from SA to any other.
7. We can't set the Model database to OFFLINE.
8. The recovery model of this database is Full. Hence, we can take full, differential, and transactional log backups.
9. The ID of the Model database is 3.
Components of the
Model Database:
The model database also contains lots of components like system tables, system views, system functions system stored procedures, etc.
Details of a few important components are given below:
System Tables: This database contains a few system tables like sys. objects, sys. columns, sys. indexes, and sys. views etc.
System Views: The model database also contains a few system views which provide easy access to system tables. For example, sys.database_files,
sys.database_permissions, and sys.database_role_members.
System Stored Procedures: A few system-stored procedures are also available in this database which helps DBA perform their administrative tasks. For example, sp_adduser, sp_helpdb and sp_addrole etc.
Functions of the Model Database:
Here are a few essential functions
of the Model database for your reference:
1. Providing a Template
2. Defining Database Properties
3. Managing Database Security
4. Supporting Customization
Conclusion:
The model database is critical so always keep good db backup in a safe location for any unprecedented issue.
No comments:
Post a Comment