SQL Server system Databases are very critical. Lets talk one at a time.
Resource database is read-only database and that contains all the system objects. like sys.objects…
it does not contain any user data or user meta data. Resource database makes u[grading to a new version much easier and faster procedure. Resource database is located under
<drive>:\programfiles\Microsoft SQL server\MSSQL\<version>.<instance_name>\mssql\binn (mssqlsystemresource.mdf)
SQL Server cannot backup and restore resource database. you can perform only file based copy. Resource database should be modified only with the direction of Microsoft support.
The id of the resource database is 32767.
–To determine the version of the resource database
— To determine twhen the resource databse was last updated
— To access SQL definations of system objects
the master database contains information about your databases logins,configurations,file locations and information about the instance.
main difference between master and resource db’s are. master database contains information about the instance and resource contains information about schema and stored procedures that needed to run your instance.
do not create any objects in master database.
temp database is used to store user temporary objects needed by the database engine and the rwo version. indexing sorting.
model database is a system database that creates a template when ever sql server creates a database. but this will not make an entry when database is restored or attached.
msdb datasbe contains inforation on sql agent jobs,agent job history,log shipping,SSIS and backup restore information., job alerts and policies.
master,msdb,model database should be backup regularly.