移动系统数据库

适用于： SQL Server

本文说明如何在 SQL Server 中移动系统数据库。 移动系统数据库在下列情况下可能会有用：

• 故障恢复。 例如，数据库处于可疑模式下或因硬件故障而关闭。

• 预先安排的重定位。

• 为预定的磁盘维护操作而进行的重定位。

下列过程适用于在同一 SQL Server 实例内移动数据库文件。 若要将数据库移动另一个 SQL Server 实例中或另一台服务器上，请使用备份和还原操作。

本文中的过程需要数据库文件的逻辑名称。 若要获取该名称，请在 sys.master_files 目录视图中查询名称列。

重要

如果移动系统数据库并随后重新生成 master 数据库，则必须再次移动系统数据库，因为重新生成操作会将所有系统数据库安装到其默认位置。

移动系统数据库

若要将移动系统数据库数据或日志文件的操作作为预先安排的重定位或预定的维护操作的一部分，请按照下列步骤操作。 其中包括 model、msdb 和 tempdb 系统数据库。

重要

此过程适用于除 master 和 Resource 数据库以外的所有系统数据库。 有关移动 master 数据库的步骤，请参阅本文的后半部分。 无法移动 Resource 数据库。

1. 通过查看 sys.master_files 目录视图，记录要移动的数据库文件的现有位置。

2. 验证 SQL Server 数据库引擎的服务帐户是否对文件的新位置具有完全权限。 有关详细信息，请参阅配置 Windows 服务帐户和权限。 如果数据库引擎服务帐户无法控制其新位置中的文件，则 SQL Server 实例不会启动。

3. 对于要移动的每个数据库文件，请运行以下语句。

   ALTER DATABASE database_name
       MODIFY FILE (NAME = logical_name, FILENAME = 'new_path\os_file_name');
   

   在重新启动服务之前，数据库将继续使用现有位置中的数据和日志文件。

4. 停止 SQL Server 实例以执行维护。 有关详细信息，请参阅启动、停止、暂停、继续和重启 SQL Server 服务。

5. 将数据库文件或文件复制到新位置。 此步骤不是 tempdb 系统数据库的必要步骤；将自动在新位置创建这些文件。

6. 重启 SQL Server 实例或服务器。 有关详细信息，请参阅启动、停止、暂停、继续和重启 SQL Server 服务。

7. 通过运行以下查询来验证文件更改。 系统数据库应报告新的物理文件位置。

   SELECT name,
          physical_name AS CurrentLocation,
          state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID(N'<database_name>');
   

8. 由于在步骤 5 中复制了数据库文件而不是移动它们，因此，现在可以从以前的位置安全删除未使用的数据库文件。

后续操作：移动 msdb 系统数据库后

如果移动了 msdb 数据库并配置了数据库邮件，请完成以下额外步骤。

1. 通过运行以下查询，验证是否已为 msdb 数据库启用 Service Broker。

   SELECT is_broker_enabled
   FROM sys.databases
   WHERE name = N'msdb';
   

   如果未为 msdb 启用 Service Broker，则必须重新启用它，以使数据库邮件正常运行。 有关详细信息，请参阅 ALTER DATABASE ... SET ENABLE_BROKER。

   ALTER DATABASE msdb
       SET ENABLE_BROKER
       WITH ROLLBACK IMMEDIATE;
   

   确认 is_broker_enabled 的值现在为 1。

2. 通过发送测试邮件来验证数据库邮件是否正常运行。

故障恢复过程

如果由于硬件故障而必须移动文件，则请按照下列步骤将文件重新定位到一个新位置。 此过程适用于除 master 和 Resource 数据库以外的所有系统数据库。 以下示例使用 Windows命令行提示符和 sqlcmd 实用工具。

重要

如果数据库无法启动、处于可疑模式或处于未恢复状态，则只有 sysadmin 固定角色的成员才可以移动该文件。

1. 验证 SQL Server 数据库引擎的服务帐户是否对文件的新位置具有完全权限。 有关详细信息，请参阅配置 Windows 服务帐户和权限。 如果数据库引擎服务帐户无法控制其新位置中的文件，则 SQL Server 实例不会启动。

2. 如果 SQL Server 实例已启动，请停止它。

3. 通过在命令提示符下输入下列命令之一，在仅 master 恢复模式下启动 SQL Server 实例。 使用启动参数 3608 可防止 SQL Server 自动启动和恢复除 master 数据库之外的任何数据库。 有关详细信息，请参阅启动参数和 TF3608。

   在这些命令中指定的参数区分大小写。 如果未按照所示指定参数，命令将失败。

   对于默认的 (MSSQLSERVER) 实例，请运行以下命令：

   NET START MSSQLSERVER /f /T3608
   

   对于命名实例，请运行以下命令：

   NET START MSSQL$instancename /f /T3608
   

   有关详细信息，请参阅启动、停止、暂停、继续和重启 SQL Server 服务。

4. 使用跟踪标志 3608 和 /f 启动服务后，立即启动与服务器的 sqlcmd 连接，以声明可用的单个连接。 例如，在与默认 (MSSQLSERVER) 实例相同的服务器上本地执行 sqlcmd 并使用 Active Directory 集成身份验证进行连接时，请运行以下命令：

   sqlcmd
   

   若要连接到本地服务器上具有 Active Directory 集成身份验证的命名实例，请执行以下操作：

   sqlcmd -S localhost\instancename
   

   有关 sqlcmd 语法的详细信息，请参阅 sqlcmd 实用工具。

   对于要移动的每个文件，请使用 sqlcmd 命令或 SQL Server Management Studio 运行以下语句。 有关使用 sqlcmd 实用工具的详细信息，请参阅 sqlcmd - 使用实用工具。 打开 sqlcmd 会话后，针对要移动的每个文件运行以下语句一次：

   ALTER DATABASE database_name
       MODIFY FILE (NAME = logical_name, FILENAME = 'new_path\os_file_name');
   GO
   

5. 退出 sqlcmd 实用工具或 SQL Server Management Studio。

6. 停止 SQL Server 实例。 例如，在命令行提示符下运行 NET STOP MSSQLSERVER。

7. 将文件复制到新位置。

8. 重新启动 SQL Server实例。 例如，在命令行提示符下运行 NET START MSSQLSERVER。

9. 通过运行以下查询来验证文件更改。

   SELECT name,
          physical_name AS CurrentLocation,
          state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID(N'<database_name>');
   

10. 由于在步骤 7 中复制了数据库文件而不是移动它们，因此，现在可以从以前的位置安全删除未使用的数据库文件。

移动 master 数据库

若要移动 master 数据库，请按照下列步骤进行操作。

1. 验证 SQL Server 数据库引擎的服务帐户是否对文件的新位置具有完全权限。 有关详细信息，请参阅配置 Windows 服务帐户和权限。 如果数据库引擎服务帐户无法控制其新位置中的文件，则 SQL Server 实例不会启动。

2. 从“开始”菜单，找到并启动“SQL Server 配置管理器”。 有关预期位置的详细信息，请参阅 SQL Server 配置管理器。

3. 在“SQL Server 服务”节点中，右键单击 SQL Server 实例（如 SQL Server (MSSQLSERVER)），并选择“属性”。

4. 在“SQL Server (instance_name) 属性”对话框中，单击“启动参数”选项卡。

5. 在“现有参数”框中，选择 -d 参数。 在“指定启动参数”框中，将该参数更改为 master 数据文件的新路径。 单击“更新”以保存更改。

6. 在“现有参数”框中，选择 -l 参数。 在“指定启动参数”框中，将该参数更改为 master 日志文件的新路径。 单击“更新”以保存更改。

   数据文件的参数值必须跟在 -d 参数的后面，日志文件的参数值必须跟在 -l 参数的后面。 下面的示例显示用于 master 数据文件默认位置的参数值。

   -dC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\master.mdf
   -lC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
   

   如果 master 数据文件预先安排的重定位是 E:\SQLData，则参数值将做如下更改：

   -dE:\SQLData\master.mdf
   -lE:\SQLData\mastlog.ldf
   

7. 选择“确定”将永久保存所作更改并关闭“SQL Server (instance_name) 属性”对话框。

8. 通过右键单击实例名称并选择“停止”来停止 SQL Server 实例。

9. 将 master.mdf 和 mastlog.ldf 文件复制到新位置。

10. 重新启动 SQL Server实例。

11. 通过运行以下查询来验证 master 数据库的文件更改。

    SELECT name,
           physical_name AS CurrentLocation,
           state_desc
    FROM sys.master_files
    WHERE database_id = DB_ID('master');
    

12. 此时 SQL Server 应正常运行。 但是 Microsoft 建议还调整 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\Setup 处的注册表项，其中 instance_ID 类似于 MSSQL13.MSSQLSERVER。 在该配置单元中，将 SQLDataRoot 值更改为 master 数据库文件新位置的新路径。 未能更新注册表可能会导致修补和升级失败。

13. 由于在步骤 9 中复制了数据库文件，而不是移动它们，因此，现在可以从以前的位置安全删除未使用的数据库文件。

移动资源数据库

Resource 数据库的位置为 \<drive>:\Program Files\Microsoft SQL Server\MSSQL\<version>.<instance_name>\MSSQL\Binn\。 无法移动数据库。

后续操作：移动所有系统数据库后

如果将所有系统数据库移动到新驱动器或卷，或者移动到具有不同驱动器号的另一台服务器，请进行以下更新。

• 更改 SQL Server 代理日志路径。 如果不更新此路径，SQL Server 代理将无法启动。

• 更改数据库默认位置。 如果指定为默认位置的驱动器号和路径不存在，则创建新数据库可能会失败。

更改 SQL Server 代理日志路径

如果已将所有系统数据库都移到新的卷或迁移到使用不同驱动器盘符的另一个服务器，且 SQL 代理错误日志文件 SQLAGENT.OUT 的路径不复存在，请进行下列更新。

1. 从 SQL Server Management Studio 的“对象资源管理器”中，展开“SQL Server 代理”。

2. 右键单击“错误日志”，然后选择“配置”。

3. 在 “配置 SQL Server 代理错误日志” 对话框中，指定 SQLAGENT.OUT 文件的新位置。 默认位置为 C:\Program Files\Microsoft SQL Server\MSSQL\<version>.<instance_name>\MSSQL\Log\。

更改数据库默认位置

1. 从 SQL Server Management Studio 的“对象资源管理器”中，连接到所需的 SQL Server 实例。 右键单击该实例，然后选择“属性”。

2. 在 “服务器属性” 对话框中，选择 “数据库设置”。

3. 在 “数据库默认位置”下，找到数据文件和日志文件的新位置。

4. 先停止然后启动 SQL Server 服务以完成更改。

示例

A. 移动 tempdb 数据库

作为预先安排的重定位的一部分，下面的示例将 tempdb 数据和日志文件移动到一个新位置。

提示

利用此机会查看 tempdb 文件以获得最佳大小和位置。 有关详细信息，请参阅在 SQL Server 中优化 tempdb 性能。

由于每次启动 SQL Server 实例时都会重新创建 tempdb，所以不必以物理方式移动数据和日志文件。 在步骤 4 中重新启动服务时，将在新位置中创建这些文件。 在重新启动服务之前，tempdb 将继续使用现有位置中的数据和日志文件。

1. 确定 tempdb 数据库的逻辑文件名称以及在磁盘上的当前位置。

   SELECT name,
          physical_name AS CurrentLocation
   FROM sys.master_files
   WHERE database_id = DB_ID(N'tempdb');
   GO
   

2. 验证 SQL Server 数据库引擎的服务帐户是否对文件的新位置具有完全权限。 有关详细信息，请参阅配置 Windows 服务帐户和权限。 如果数据库引擎服务帐户无法控制其新位置中的文件，则 SQL Server 实例不会启动。

3. 使用 ALTER DATABASE更改每个文件的位置。

   USE master;
   GO
   
   ALTER DATABASE tempdb
       MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
   GO
   
   ALTER DATABASE tempdb
       MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
   GO
   

   在重新启动服务之前，tempdb 将继续使用现有位置中的数据和日志文件。

4. 停止再重新启动 SQL Server的实例。

5. 验证文件更改。

   SELECT name,
          physical_name AS CurrentLocation,
          state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID(N'tempdb');
   

6. 将未使用的 tempdb 文件从其原始位置删除。

相关内容

• 资源数据库
• tempdb 数据库
• master 数据库
• msdb 数据库
• 模型数据库
• 移动用户数据库
• 移动数据库文件
• 启动、停止、暂停、继续和重启 SQL Server 服务
• ALTER DATABASE (Transact-SQL)
• 重新生成系统数据库