主页 / dba / 问题 / 259510
Accepted
Ronaldo
Asked: 2020-02-13 04:25:59 +0800 CST

master、tempdb、model 和 msdb 是否有可能分别有一个不同于 1、2、3、4 的 database_id？

  • 772

据我所知， SQL Server System Databases始终具有相同的 ID，并且我在 Internet 上看到许多维护脚本依赖谓词WHERE database_id > 4将它们从脚本的操作中排除。

另外，如果我SELECT name, schema_id FROM sys.schemas;在一个新的用户数据库上运行，我会得到：

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

我在两个不同的实例上运行该查询，一个是 SQL Server 2016，另一个是 SQL Server 2005，两者都返回了相同的结果。

问题：

  1. 是否存在系统数据库 master、tempdb、model 和 msdb 的 database_id 分别不是 1、2、3、4 的情况（或 sql server 版本）？
  2. 我真的可以相信我列出的架构在任何 SQL Server 实例上总是具有相同的 ID，以便我可以根据这些 ID 编写维护脚本吗？
sql-server system-databases

3 个回答

  1. Best Answer

    理论上

    以下操作仅用于测试目的。如果您对您的数据库实例变得不可用、无响应和/或您的数据丢失不满意，请不要尝试任何这些

    分离master数据库以尝试重用它的 database_id 是不可能的。

    分离model 将导致实例崩溃并且您无法将其附加回来。这导致您必须重建所有系统数据库。此过程会删除所有信息，例如登录名、工作......并且您的用户数据库已分离。

    分离Tempdb以尝试重用它的 database_id，方法是将其附加到不同的名称下，导致 database_id 2 未被重用且tempdb无法正常工作。

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

    我能够将名称改回 tempdb，但实例非常混乱，我无法正常启动它。

    找不到数据库 ID 2。数据库可能尚未激活或正在转换中。数据库可用后重新发出查询。

    --->重建所有系统数据库。

    但是，理论上有 2 个（或更多）选项可以更改msdb系统数据库 ID：

    msdb您可以通过分离msdb、创建新用户数据库并最终创建新数据库来更改数据库 ID，msdb同时按照记录的步骤创建新的 msdb 数据库。

    您可以msdb通过分离来更改数据库 ID msdb，以不同的名称重新附加它并将名称更改回msdb. 不支持，请不要这样做

    首先以“仅主恢复模式”启动 sql server

    NET START MSSQL$InstanceName /f /T3608"

    分离 msdb 并创建一个新数据库

    EXEC sp_detach_db msdb;
GO
CREATE DATABASE test3;
GO

    修改msdb文件名，正常重启实例

    初始化 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"£

    查看数据库ID：

    SELECT name,database_id 
FROM sys.Databases;

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

    不支持的方法

    2 正常的附加程序 sp_attach_db，create database ... for attach在尝试附加时不起作用msdb。

    错误信息：

    FILESTREAM 数据库选项不能在系统数据库上设置，例如 'msdb

    您可以做的，仅用于测试目的是创建一个用户数据库，msdb2

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

    将数据库重命名为msdb

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

    正常重启实例

    NET STOP MSSQL$InstanceName
NET START MSSQL$InstanceName

    并查看database_id's：

    SELECT name, database_id 
FROM sys.databases;

    结果

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

    在实践中

    幸运的是，我在任何地方都没有看到这一点，但 database_id 4 理论上可以被用户数据库重用。如果发生这种情况，在我的查询中过滤掉系统数据库可能是我最少的问题。

    也就是说，使用WHERE DB_NAME(database_id) NOT IN ('master','tempdb','model','msdb','distribution','ssisdb')是肯定的选择。

    • 16

  2. 实际上，对于所有当前发布的 SQL Server 版本，这些 ID 将是相同的。

    请注意，distribution&SSISDB数据库也是系统数据库，但不会有可预测的 database_id。

    但是，不能保证这种情况会一直如此，或者将来会继续如此。Microsoft 不提供文档，也不保证这些 ID 始终相同。可以保证有人已经进入数据库并弄乱了这些系统模式。

    在 SQL Server 的下一个版本中，产品团队可以决定对 ID 编号方案进行更改。该更改可以反向移植到 Service Pack 或累积更新中的现有版本。

    这在不久的将来可能不太可能发生，因此您可能不必担心。但是，如果您编写了很棒的代码，那么您的代码可能在一两年后仍然在使用，那时这种情况确实发生了。然后，你的继任者将有一个老鼠窝需要解开，你的代码不再是“很棒的代码”。

    撇开未来的变化不谈，依赖于神秘的硬编码 ID 值的代码是不可读的。不是每个人都知道这schema_id > 16384表示一系列特殊模式。（自世纪之交以来我一直在使用 SQL Server，我会被那行代码弄糊涂。）

    让你的代码读起来像散文一样，你将拥有更好、更易于维护的代码。

    • 10

  3. 这些数据库是在安装时创建的，它们被指定为您编写的 1、2、3、4。如果你干预它，你总是可以破坏一个系统，微软可以改变这些数据库，无论是在 ID 上还是在目的上。MS 可以model为不同的目的创建第二个数据库，称为model2，或添加第二个tempdb数据库来拆分不同类型的临时表，或出于任何原因。

    我的建议是：请放心，在当前版本的 SQL Server 中，这些数据库 ID 是一致的（在安装时）。我也从未见过他们。如果您特别担心或无法摆脱那种烦人的感觉，请在程序/脚本的开头验证这些事实，特别是 ID # = db name string。

    我喜欢在编写代码时跟踪我在代码中所做的假设。对于内部代码，这些数据库名称和 ID 假设非常安全。对于外部客户，我更喜欢验证一切。它有助于 a) 摆脱你的头脑，无论是这个数据库名称/ID 假设还是其他什么，并且 b) 如果其中一个假设确实被打破，你可以很快找到它。它可以省去很多麻烦，并且知道您可以依靠什么是令人放心的。

    当您遇到这些问题时，只需验证并继续前进（尽管发布问题也没有什么害处，就像您所做的那样）。通过这种方式，您可以更好地将时间花在实际问题上，并且您可以清除这些烦人的问题。当事情失败时，它会给你信心，这不是这些经过验证的问题之一。

    在这种特殊情况下，我从未觉得需要验证这些数据库 ID。我总是通过它们的功能而不是直接访问它们。但是，如果我正在编写一个脚本来操作这些数据库，如果环境不是我所期望的那样，后果可能会很严重，我肯定会验证我的假设。

    • 3

相关问题