Is it possible for master, tempdb, model and msdb to have a database_id other than 1,2,3,4 respectively?

Question

SQL Server System Databases, as far as I know, always have those same IDs and I have seen lots of maintenance scripts on the internet relying on the predicate WHERE database_id > 4 to exclude them from the actions of the script.

Also, if I run SELECT name, schema_id FROM sys.schemas; on a new user database, I get:

name                 schema_id
dbo                  1
guest                2
INFORMATION_SCHEMA   3
sys                  4
db_owner             16384
db_accessadmin       16385
db_securityadmin     16386
db_ddladmin          16387
db_backupoperator    16389
db_datareader        16390
db_datawriter        16391
db_denydatareader    16392
db_denydatawriter    16393

I ran that query on two different instances, one a SQL Server 2016 and the other a SQL Server 2005, and both returned that same result.

Questions:

1. Is there any situation (or sql server version) in which the system databases master, tempdb, model and msdb would have a database_id other than 1,2,3,4 respectively?
2. Can I really trust on the fact that the schemas I listed will always have the same IDs on any instance of SQL Server so that I can write maintenance scripts based on those IDs?

Answer 1

In theory

Below actions are only for testing purposes. Do not attempt any of this if you are not ok with your database instance becoming unusuable, unresponsive and/or your data being lost

Detaching the master database to try and reuse it's database_id is not possible.

Detaching model will result in the instance crashing and you not able to attach it back. This results in you having to Rebuild all your system databases. This process removes all information such as logins, jobs, .... And your user databases are detached.

Detaching Tempdb to try and reuse it's database_id's by attaching it back under a different name resulted in the database_id 2 not being reused & tempdb not working.

name    database_id
master  1
model   3
msdb    4
test3_2__   5
test3_2_    6
tempdb  7

I was able to change the name back to tempdb, but the instance was pretty messed up, and I was unable to start it normally.

Could not find database ID 2. Database may not be activated yet or may be in transition. Reissue the query once the database is available.

---> Rebuild all your system databases.

But, in theory there are 2 (or more) options to change the msdb system database id:

You can change the msdb database id by detaching msdb, creating a new user database and finally creating a new msdb database while following the documented steps to create a new msdb database.

You can change the msdb database id by detaching msdb, reattaching it under a different name and changing the name back to msdb. UNSUPPORTED, please do not do this

First start sql server in 'master only recovery mode'

NET START MSSQL$InstanceName /f /T3608"

Detach msdb and create a new database

EXEC sp_detach_db msdb;
GO
CREATE DATABASE test3;
GO

Change the names of msdb files, Restart the instance normally

Initialize msdb:

SQLCMD -E -S<servername> -i"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.out"£

View the database id's:

SELECT name,database_id 
FROM sys.Databases;

name    database_id
master  1
tempdb  2
model   3
test3   4
...
msdb    12

Unsupported method

The 2 normal attach procedures sp_attach_db and create database ... for attach do not work when trying to attach msdb.

Error message:

FILESTREAM database options cannot be set on system databases such as 'msdb

What you can do, solely for testing purposes is create a user database, msdb2

CREATE DATABASE msdb2  
    ON (FILENAME = N'X:\MSSQL\DATA\MSDBData.mdf'),   
    (FILENAME = N'X:\MSSQL\DATA\MSDBLog.ldf')   
    FOR ATTACH;  

Rename the database to msdb

ALTER DATABASE msdb2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE msdb2 MODIFY NAME = msdb ;
GO  
ALTER DATABASE msdb SET MULTI_USER

Restart the instance normally

NET STOP MSSQL$InstanceName
NET START MSSQL$InstanceName

and view the database_id's:

SELECT name, database_id 
FROM sys.databases;

Result

name    database_id
master  1
tempdb  2
model   3
test    4
...
msdb    10

In practice

I have fortunately not seen this anywhere, but database_id 4 could in theory be reused by user databases. If this happened filtering out system databases in my queries would probably be the least of my problems.

That said, using WHERE DB_NAME(database_id) NOT IN ('master','tempdb','model','msdb','distribution','ssisdb') is the sure choice.

Answer 2

Practically speaking, those IDs are going to be the same for all currently released versions of SQL Server.

Note that the distribution & SSISDB databases are also system databases, but will not have a predictable database_id.

However, there are no guarantees that this will always be the case, or that this will continue to be the case in the future. Microsoft does not provide documentation nor guarantees that these IDs are always the same. There are guarantees that someone has gone into a database and messed with these system schemas.

In the next version of SQL Server, the Product team could decide to make a change to the ID number scheme. That change could be backported to existing versions in a Service Pack or Cumulative Update.

This might be unlikely in the near future, so you might not care to worry about it. But if you write awesome code, your code may still be in use a decade or two from now, when this does happen. And then, your successors will have a rat's nest to untangle, and your code ceases to be "awesome code."

Future changes aside, code that depends on cryptic, hard coded ID values is not readable. Not everyone knows that schema_id > 16384 indicates a range of special schemas. (I've been using SQL Server since the turn of the century and I would be confused by that line of code.)

Make your code read like prose, and you'll have better, more maintainable code.

Answer 3

These databases are created at installation and they're specified to be 1,2,3,4 as you wrote. You can always break a system if you meddle with it, and Microsoft can change these databases, both in IDs and in purpose. MS could make a second model db for different purposes called model2, or add a second tempdb database to split different types of temporary tables, or for whatever reason.

My advice is: be reassured that in current versions of SQL Server these database IDs are consistent (at installation). I have also never seen them otherwise. If you are particularly concerned or just can't shake that nagging feeling, verify these facts at the beginning of your program/script, specifically ID # = db name string.

I like to track the assumptions that I make in code as I write it. For in-house code, these db name and ID assumptions are pretty safe. For outside clients I prefer to verify everything. It helps a) get it out of your head, whether it's this db name/ID assumption or something else, and b) if one of these assumptions does break, you can find it very fast. It saves a lot of head scratching, and it's reassuring to know what you can count on.

When you hit these questions, just verify and move on (although there's no harm in posting the question too, like you did). In this way your time is better spent on actual issues, and you can clear your head of these nagging questions. And it gives you confidence when things fail, it's not one of these verified questions.

In this particular case, I've never felt the need to verify these database IDs; I've always accessed them via their functionality, not directly. However, if I were writing a script to operate on these databases, knowing the ramifications could be severe if the environment is not what I expect it to be, I would definitely verify my assumptions.