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

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:

• 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?

• 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?

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 -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.

StackExchange Database Administrators Q#259510, answer score: 16