In this post I want to give a basic explanation of what system databases are and things we can or cannot do with them 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!