SQL Server System Databases

SQL Server system Databases are very critical. Lets talk one at a time.

Resource Database.

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.

 

/*Resource Database*/

–To determine the version of the resource database

SELECT SERVERPROPERTY(‘ResourceVersion’)
GO;
— To determine twhen the resource databse was last updated

SELECT SERVERPROPERTY(‘ResourceLastUpdateDateTime’)
GO;
— To access SQL definations of system objects

SELECT OBJECT_DEFINITION(OBJECT_ID(‘sys.objects’))

 

master database:

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:

temp database is used to store user temporary objects needed by the database engine and the rwo version. indexing sorting.

model database: 

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 databse:

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.

 

 

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s