SQL Server – System databases – Master,MSDB,Model,Resource and tempdb

System databases are default database after sql server installation, master,msdb,model, resource and tempdb are part of system database. All sql server information are stored in system database. using system catalog or system table you can read sql server internal information.

System Database

master, model, msdb, resource and tempdb database.

Master database in sql server

Master database stores all information related to sql server. It includes instances, linked server, logins and database server configuration setting related information. Using master database you can create new user database, it stores all database file location in “sys.master_files” table as well as start-up parameter details related to sql server.

Limitation of master database:

Cannot add files or file groups.
Changing collation for master database is not allowed. Default collation is applied on server.
Changing database is not allowed for master database, default owner is dbo.
Full-text catalog or full text index cannot be created on master database.
Triggers on system tables are not allowed.
Can’t drop master database.
Dropping the guest user from the database is not allowed.
Database is not possible for master database.
Dropping the primary file group, data file or log file are not allowed.
Master database can’t be in offline mode.
Can’t set master database in Read_only mode.


More details about SQL Server Model database -> http://www.sqlserver-query.com/model-database-in-sql-server/

Leave a Reply




4 + 4 =