移动系统数据库

  • 项目

适用于：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 文件从其原始位置删除。

另请参阅

后续步骤