Jul
18
2018

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.

Thanks for reading!
 

Leave a Reply

Your email address will not be published. Required fields are marked *