Categories

SQL Server system databases 101

It was the other day during the networking time in my SQL User group meeting when someone told the story of the new DBA that was joking about running “DROP DATABASE [master]” and there were a few different reactions about it, from horror faces to laughs.

But that gave me the idea for this post, where I want to make a short lit of things that we can do to them or otherwise we are not allowed.

System Databases

First thing first, so let’s see which are the system databases and what their functionality is.

Every installation of SQL Server comes with some databases installed by default. These can be found in SQL Server Management Studio in the Object Explorer listed under the folder “System Databases”

We can see four databases here, [master], [model], [msdb] and [tempdb]. Each of them serves a purpose within the database engine, which I will explain in a minute.

There is another databases which is even more special that it can’t even be found here and it’s not accessible in the same way as the others, that is the resource database (mssqlsystemresource)

What are system databases for

As I said, every system database serves a different purpose, we can see the full reference in Books online, but if you feel lazy, that’s why I’m here

master, records system-level information for an instance of SQL Server, that include logon accounts, endpoints, linked servers, and system configuration settings

model, is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward

msdb, used by SQL Server Agent for scheduling alerts and jobs

tempdb, is a workspace for holding temporary objects or intermediate result sets

mssqlsystemresource, is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

This is a very high level description of what system databases are for, so I encourage you go read more in Books online

But the purpose of this post is to enumerate what can or can’t be done with these system databases and to answer quickly to the question that was asked, no, you cannot drop master … thankfully 🙂

Most of the databases have a similar pattern, so a lot of things that might compromise the whole system will return an error.

I found that [msdb] seems to be more flexible and allow you to do things that might be really stupid, like setting it to READ_ONLY. This will prevent the SQL Agent to start and if running, to add new jobs or even running them.

Also we can add filegroups and set them to be the DEFAULT, not sure why anyone would want to do that, since we shouldn’t create user tables in system databases anyway.

[tempdb] for instance, cannot be backed up or restored, which kind of make sense along with changing the recovery model or run certain consistency checks. Funny enough you can run CHECKDB but you can’t run some of the commands that run within it, CHECKALLOC and CHECKCATALOG.

The full list of things we cannot do is as follows.

tempdb

master

Model

msdb

Changing collation. The default collation is the server collation.

Dropping the database.

Dropping the guest user from the database.

Enabling change data capture.

Participating in database mirroring.

Removing the primary filegroup, primary data file, or log file.

Renaming the database or primary filegroup.

Setting the database to OFFLINE.

Setting the database or primary filegroup to READ_ONLY.

Adding filegroups.

X

Changing the database owner

X

Backing up or restoring the database.

Creating a full-text catalog or full-text index.

Creating procedures, views, or triggers using the WITH ENCRYPTION option. The encryption key is tied to the database in which the object is created. Encrypted objects created in the model database can only be used in model.

X

Creating a database snapshot.

Creating triggers on system tables in the database.

X

X

Running DBCC CHECKALLOC.

X

X

X

Running DBCC CHECKCATALOG.

X

X

X

Also each database come with default settings which can be changed or not, depending on which database we are.

Here I find more flexibility on [model], because it is the template for every database created without specifying its settings, so it allows to customize new databases. To be honest I believe every database should be customized according to their specific requirements, so every default setting would be just as bad as those coming out of the box.

master

msdb

model

tempdb

Database option

Default value

Can be modified

Default value

Can be modified

Default value

Can be modified

Default value

Can be modified

ALLOW_SNAPSHOT_ISOLATION

ON

No

ON

No

OFF

Yes

OFF

Yes

ANSI_NULL_DEFAULT

OFF

Yes

OFF

Yes

OFF

Yes

OFF

Yes

ANSI_NULLS

OFF

Yes

OFF

Yes

OFF

Yes

OFF

Yes

ANSI_PADDING

OFF

Yes

OFF

Yes

OFF

Yes

OFF

Yes

ANSI_WARNINGS

OFF

Yes

OFF

Yes

OFF

Yes

OFF

Yes

ARITHABORT

OFF

Yes

OFF

Yes

OFF

Yes

OFF

Yes

AUTO_CLOSE

OFF

No

OFF

Yes

OFF

Yes

OFF

No

AUTO_CREATE_STATISTICS

ON

Yes

ON

Yes

ON

Yes

ON

Yes

AUTO_SHRINK

OFF

No

OFF

Yes

OFF

Yes

OFF

No

AUTO_UPDATE_STATISTICS

ON

Yes

ON

Yes

ON

Yes

ON

Yes

AUTO_UPDATE_STATISTICS_ASYNC

OFF

Yes

OFF

Yes

OFF

Yes

OFF

Yes

CHANGE_TRACKING

OFF

No

OFF

No

OFF

No

OFF

No

CONCAT_NULL_YIELDS_NULL

OFF

Yes

OFF

Yes

OFF

Yes

OFF

Yes

CURSOR_CLOSE_ON_COMMIT

OFF

Yes

OFF

Yes

OFF

Yes

OFF

Yes

CURSOR_DEFAULT

GLOBAL

Yes

GLOBAL

Yes

GLOBAL

Yes

GLOBAL

Yes

Database Availability Options

ONLINE

No

ONLINE

No

ONLINE

No

ONLINE

No

MULTI_USER

No

MULTI_USER

Yes

MULTI_USER

Yes

MULTI_USER

No

READ_WRITE

No

READ_WRITE

Yes

READ_WRITE

Yes

READ_WRITE

No

DATE_CORRELATION_OPTIMIZATION

OFF

Yes

OFF

Yes

OFF

Yes

OFF

Yes

DB_CHAINING

ON

No

ON

Yes

OFF

No

ON

No

ENCRYPTION

OFF

No

OFF

No

OFF

No

OFF

No

MIXED_PAGE_ALLOCATION

ON

No

ON

No

ON

No

OFF

No

NUMERIC_ROUNDABORT

OFF

Yes

OFF

Yes

OFF

Yes

OFF

Yes

PAGE_VERIFY

CHECKSUM

Yes

CHECKSUM

Yes

CHECKSUM

Yes

CHECKSUM for new installations of SQL Server./NONE for upgrades of SQL Server.

Yes

PARAMETERIZATION

SIMPLE

Yes

SIMPLE

Yes

SIMPLE

Yes

SIMPLE

Yes

QUOTED_IDENTIFIER

OFF

Yes

OFF

Yes

OFF

Yes

OFF

Yes

READ_COMMITTED_SNAPSHOT

OFF

No

OFF

No

OFF

Yes

OFF

No

RECOVERY

SIMPLE

Yes

SIMPLE

Yes

Depends on SQL Server edition*

Yes

SIMPLE

No

RECURSIVE_TRIGGERS

OFF

Yes

OFF

Yes

OFF

Yes

OFF

Yes

Service Broker Options

DISABLE_BROKER

No

ENABLE_BROKER

Yes

DISABLE_BROKER

No

ENABLE_BROKER

Yes

TRUSTWORTHY

OFF

Yes

ON

Yes

OFF

No

OFF

No

Backup and Restore

I don’t want to finish this post without having a special mention to the backup and restore process.

Backing up system databases (except [tempdb] and Resource) is just as any other database and is strongly recommended.

To restore system databases we have on one side [model] and [msdb] that can be restored just as any other user database because there are no special requirements, [tempdb] cannot be backed up or restored, so we only have to left, [master] and [Resource] databases.

To restore [master], we need to start SQL Server in single-user mode before attempting it, the process is explained in Books Online so I won’t expend much time on it, but I recommend attempting it in a non-production environment to practice in case you need it.

And finally [Resource] database which since it’s not accessible like the others, it can’t be restored as the others. This database can be backed up and restore in a file-base manner, so you need to stopped SQL Server to be able to replace with a previously taken copy of the file, otherwise you’ll get an error.

Conclusion

System databases are vital for the correct functioning of every SQL Server instance and each of them serve a purpose.

Best practices are to maintain them like any other user database in terms of performing backups, consistency checks and so on, and I wouldn’t recommend to create user tables or procedures (or functions) there. I know that many community scripts use by default [master], but you should have a dedicated database for DBA maintenance instead.

Hopefully you found this interesting and if you have any question, use the comments box below.