MS SQL Server System Databases

The Resource Database
SQL Server added the Resource database. This database contains all the read-only critical system tables, metadata, and stored procedures that SQL Server needs to run. It does not contain any information about your instance or your databases, because it is only written to during an installation of a new service pack.
The Resource database contains all the physical tables and stored procedures referenced
logically by other databases. The database can be found by default in C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\Binn.mdf and .ldf, and there is only one Resource database perinstance.
The use of drive C: in the path assumes a standard setup. If your machine is set up differently, you may
need to change the path to match your setup. Additionally, the .MSSQLSERVER is the instance name.
If your instance name is different, use your instance name in the path.
In SQL Server 2000, when you upgraded to a new service pack, you would need to run many long scripts to drop and recreate system objects. This process took a long time to run and created an environment hat couldn’t be rolled back to the previous release after the service pack. In SQL Server 2008, when you pgrade to a new service pack or quick fix, a copy of the Resource database overwrites the old database.
This enables you to both quickly upgrade your SQL Server catalog and roll back a release.
The Resource database cannot be seen through Management Studio and should never be altered unless you’re under instruction to do so by Microsoft Product Support Services (PSS). You can connect to the database under certain single-user mode conditions by typing the command USE MSSQLSystemResource.
Typically, a DBA runs simple queries against it while connected to any database, instead of having to connect to the resource database directly. Microsoft provides some functions which allow this access. For example, if you were to run this query while connected to any database, it would return your Resource database’s version and the last time it was upgraded:
SELECT serverproperty(‘resourceversion’) ResourceDBVersion,
serverproperty(‘resourcelastupdatedatetime’) LastUpdateDate Do not place the Resource database on an encrypted or compressed drive. Doing this may cause upgrade or performance issues.


The master Database
The master database contains the metadata about your databases (database configuration and file location),logins, and configuration information about the instance. You can see some of the metadata stored in master by running the following query, which returns information about the databases that exist onthe server:SELECT * FROM sys.databasesThe main difference between the Resource and master databases is that the master database holds dataspecific to your instance, whereas the Resource database just holds the schema and stored proceduresneeded to run your instance, but does not contain any data specific to your instance. You should alwaysback up the master database after creating a new database, adding a login, or changing the configurationof the server.


tempdb Database
The tempdb database is similar to the operating system paging file. It’s used to hold temporary objects created by users, temporary objects needed by the database engine, and row-version information. The tempdb database is created each time you restart SQL Server. The database will be recreated to be its original database size when the SQL Server is stopped. Because the database is recreated each time, there is no reason to back it up. Data changes made to objects in the tempdb database benefit from reduced logging.
It is important to have enough space allocated to your tempdb database, because many operations that you will use in your database applications use the tempdb. Generally speaking, you should set tempdb to autogrow as it needs space. If there is not enough space, the user may receive one of the following errors:
❑ 1101 or 1105: The session connecting to SQL Server must allocate space in tempdb.
❑ 3959: The version store is full.
❑ 3967: The version store must shrink because tempdb is full.


model Database
model is a system database that serves as a template when SQL Server creates a new database. As each database is created, SQL Server copies the model database as the new database. The only time this does not apply is when you restore or attach a database from a different server.
If a table, stored procedure, or database option should be included in each new database that you create on a server, you may simplify the process by creating the object in model. When the new database is created, model is copied as the new database, including the special objects or database settings you have added to the model database. If you add your own objects to model, model should be included in your backups, or you should maintain a script which includes the changes.

msdb Database
msdb is a system database that contains information used by SQL Server agent, log shipping, SSIS, and the backup and restore system for the relational database engine. The database stores all the information about jobs, operators, alerts, and job history. Because it contains this important system-level data, you should back up this database regularly