移动系统数据库Move System Databases

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主题说明如何在 SQL ServerSQL Server中移动系统数据库。This topic describes how to move system databases in SQL ServerSQL Server. 移动系统数据库在下列情况下可能很有用:Moving system databases may be useful in the following situations:

  • 故障恢复。Failure recovery. 例如,数据库处于可疑模式下或因硬件故障而关闭。For example, the database is in suspect mode or has shut down because of a hardware failure.

  • 预先安排的重定位。Planned relocation.

  • 为预定的磁盘维护操作而进行的重定位。Relocation for scheduled disk maintenance.

下列过程适用于在同一 SQL ServerSQL Server实例内移动数据库文件。The following procedures apply to moving database files within the same instance of SQL ServerSQL Server. 若要将数据库移动另一个 SQL ServerSQL Server 实例中或另一台服务器上,请使用 备份和还原 操作。To move a database to another instance of SQL ServerSQL Server or to another server, use the backup and restore operation.

本主题中的过程需要数据库文件的逻辑名称。The procedures in this topic require the logical name of the database files. 若要获取该名称,请在 sys.master_files 目录视图中查询名称列。To obtain the name, query the name column in the sys.master_files catalog view.

重要

如果移动系统数据库并且随后重新生成 master 数据库,则必须再次移动系统数据库,因为重新生成操作会在默认位置安装所有系统数据库。If you move a system database and later rebuild the master database, you must move the system database again because the rebuild operation installs all system databases to their default location.

重要

移动文件之后, SQL ServerSQL Server 服务帐户必须有权访问新文件的文件夹位置中的文件。After moving files, the SQL ServerSQL Server service account must have permission to access the files in new file folder location.

预先安排的重定位与预定的磁盘维护过程Planned Relocation and Scheduled Disk Maintenance Procedure

若要将移动系统数据库数据或日志文件的操作作为预先安排的重定位或预定的维护操作的一部分,请按照下列步骤操作。To move a system database data or log file as part of a planned relocation or scheduled maintenance operation, follow these steps. 此过程适用于除 master 和 Resource 数据库以外的所有系统数据库。This procedure applies to all system databases except the master and Resource databases.

  1. 对于要移动的每个文件,请运行以下语句。For each file to be moved, run the following statement.

    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )  
    
  2. 停止 SQL ServerSQL Server 实例或关闭系统以执行维护。Stop the instance of SQL ServerSQL Server or shut down the system to perform maintenance. 有关详细信息,请参阅 启动、停止、暂停、继续、重启 SQL Server 服务For more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service.

  3. 将文件移动到新位置。Move the file or files to the new location.

  4. 重新启动 SQL ServerSQL Server 实例或服务器。Restart the instance of SQL ServerSQL Server or the server. 有关详细信息,请参阅 启动、停止、暂停、继续、重启 SQL Server 服务For more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service.

  5. 通过运行以下查询来验证文件更改。Verify the file change by running the following query.

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

如果移动了 msdb 数据库并为 SQL ServerSQL Server 数据库邮件 配置了实例,则请完成下列附加步骤。If the msdb database is moved and the instance of SQL ServerSQL Server is configured for Database Mail, complete these additional steps.

  1. 通过运行以下查询,验证是否已为 msdb 数据库启用 Service BrokerService BrokerVerify that Service BrokerService Broker is enabled for the msdb database by running the following query.

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

    有关启用 Service BrokerService Broker的详细信息,请参阅 ALTER DATABASE (Transact-SQL)中移动系统数据库。For more information about enabling Service BrokerService Broker, see ALTER DATABASE (Transact-SQL).

  2. 通过发送测试邮件来验证数据库邮件是否正常运行。Verify that Database Mail is working by sending a test mail.

故障恢复过程Failure Recovery Procedure

如果由于硬件故障而必须移动文件,则请按照下列步骤将文件重新定位到一个新位置。If a file must be moved because of a hardware failure, follow these steps to relocate the file to a new location. 此过程适用于除 master 和 Resource 数据库以外的所有系统数据库。This procedure applies to all system databases except the master and Resource databases.

重要

如果数据库无法启动,即处于可疑模式下或处于未恢复状态,则只有 sysadmin 固定角色的成员才可以移动该文件。If the database cannot be started, that is it is in suspect mode or in an unrecovered state, only members of the sysadmin fixed role can move the file.

  1. 如果启动了 SQL ServerSQL Server 实例,则将其停止。Stop the instance of SQL ServerSQL Server if it is started.

  2. 通过在命令提示符下输入下列命令之一,在仅 master 恢复模式下启动 SQL ServerSQL Server 实例。Start the instance of SQL ServerSQL Server in master-only recovery mode by entering one of the following commands at the command prompt. 在这些命令中指定的参数区分大小写。The parameters specified in these commands are case sensitive. 如果未按所示方式指定参数,则命令会失败。The commands fail when the parameters are not specified as shown.

    • 对于默认的 (MSSQLSERVER) 实例,请运行以下命令:For the default (MSSQLSERVER) instance, run the following command:

      NET START MSSQLSERVER /f /T3608
      
    • 对于命名实例,请运行以下命令:For a named instance, run the following command:

      NET START MSSQL$instancename /f /T3608
      

    有关详细信息,请参阅 启动、停止、暂停、继续、重启 SQL Server 服务For more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service.

  3. 对于要移动的每个文件,请使用 sqlcmd 命令或 SQL Server Management StudioSQL Server Management Studio 运行以下语句。For each file to be moved, use sqlcmd commands or SQL Server Management StudioSQL Server Management Studio to run the following statement.

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

    有关使用 sqlcmd 实用工具的详细信息,请参阅 使用 sqlcmd 实用工具For more information about using the sqlcmd utility, see Use the sqlcmd Utility.

  4. 退出 sqlcmd 实用工具或 SQL Server Management StudioSQL Server Management StudioExit the sqlcmd utility or SQL Server Management StudioSQL Server Management Studio.

  5. 停止 SQL ServerSQL Server实例。Stop the instance of SQL ServerSQL Server. 例如,运行 NET STOP MSSQLSERVERFor example, run NET STOP MSSQLSERVER.

  6. 将文件移动到新位置。Move the file or files to the new location.

  7. 重新启动 SQL ServerSQL Server实例。Restart the instance of SQL ServerSQL Server. 例如,运行 NET START MSSQLSERVERFor example, run NET START MSSQLSERVER.

  8. 通过运行以下查询来验证文件更改。Verify the file change by running the following query.

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

移动 master 数据库Moving the master Database

若要移动 master 数据库,请按下列步骤进行操作。To move the master database, follow these steps.

  1. “开始” 菜单中,依次指向 “所有程序”“Microsoft SQL Server”“配置工具” ,然后单击 “SQL Server 配置管理器”From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.

  2. 在“SQL Server 服务” 节点中,右键单击 SQL ServerSQL Server 实例(如 SQL Server (MSSQLSERVER) ),并选择“属性” 。In the SQL Server Services node, right-click the instance of SQL ServerSQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.

  3. 在“SQL Server (instance_name) 属性” 对话框中,单击“启动参数” 选项卡。In the SQL Server (instance_name) Properties dialog box, click the Startup Parameters tab.

  4. 在“现有参数”框中,选择 –d 参数以移动 master 数据文件 。In the Existing parameters box, select the -d parameter to move the master data file. 单击 “更新” 以保存更改。Click Update to save the change.

    在“指定启动参数” 框中,将该参数更改为 master 数据库的新路径。In the Specify a startup parameter box, change the parameter to the new path of the master database.

  5. 在“现有参数”框中,选择 –l 参数以移动 master 日志文件 。In the Existing parameters box, select the -l parameter to move the master log file. 单击 “更新” 以保存更改。Click Update to save the change.

    在“指定启动参数” 框中,将该参数更改为 master 数据库的新路径。In the Specify a startup parameter box, change the parameter to the new path of the master database.

    数据文件的参数值必须跟在 -d 参数的后面,日志文件的参数值必须跟在 -l 参数的后面。The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. 下面的示例显示用于 master 数据文件默认位置的参数值。The following example shows the parameter values for the default location of the master data file.

    -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,则参数值将做如下更改:If the planned relocation for the master data file is E:\SQLData, the parameter values would be changed as follows:

    -dE:\SQLData\master.mdf

    -lE:\SQLData\mastlog.ldf

  6. 通过右键单击实例名称并选择“停止” 来停止 SQL ServerSQL Server 实例。Stop the instance of SQL ServerSQL Server by right-clicking the instance name and choosing Stop.

  7. 将 master.mdf 和 mastlog.ldf 文件移动到新位置。Move the master.mdf and mastlog.ldf files to the new location.

  8. 重新启动 SQL ServerSQL Server实例。Restart the instance of SQL ServerSQL Server.

  9. 通过运行以下查询,验证 master 数据库的文件更改。Verify the file change for the master database by running the following query.

    SELECT name, physical_name AS CurrentLocation, state_desc  
    FROM sys.master_files  
    WHERE database_id = DB_ID('master');  
    GO  
    
  10. 此时 SQL Server 应正常运行。At this point SQL Server should run normally. 但是 Microsoft 建议还调整 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\Setup 处的注册表项,其中 instance_ID 类似于 MSSQL13.MSSQLSERVERHowever Microsoft recommends also adjusting the registry entry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\Setup, where instance_ID is like MSSQL13.MSSQLSERVER. 在该配置单元中,将 SQLDataRoot 值更改为新路径。In that hive, change the SQLDataRoot value to the new path. 未能更新注册表可能会导致修补和升级失败。Failure to update the registry can cause patching and upgrading to fail.

移动 Resource 数据库Moving the Resource Database

Resource 数据库的位置是 <drive>:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\。The location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\. 无法移动该数据库。The database cannot be moved.

后续任务:移动所有系统数据库后Follow-up: After Moving All System Databases

如果已将所有系统数据库都移到新的驱动器/卷或移到使用不同驱动器盘符的另一个服务器,请进行下列更新。If you have moved all of the system databases to a new drive or volume or to another server with a different drive letter, make the following updates.

  • 更改 SQL Server 代理日志路径。Change the SQL Server Agent log path. 如果不更新此路径,SQL Server 代理将无法启动。If you do not update this path, SQL Server Agent will fail to start.

  • 更改数据库默认位置。Change the database default location. 如果指定为默认位置的驱动器盘符和路径不存在,则可能无法创建新的数据库。Creating a new database may fail if the drive letter and path specified as the default location do not exist.

更改 SQL Server 代理日志路径Change the SQL Server Agent Log Path

  1. 从 SQL Server Management Studio 的对象资源管理器中,展开 “SQL Server 代理”From SQL Server Management Studio, in Object Explorer, expand SQL Server Agent.

  2. 右键单击 “错误日志” ,然后单击 “配置”Right-click Error Logs and click Configure.

  3. “配置 SQL Server 代理错误日志” 对话框中,指定 SQLAGENT.OUT 文件的新位置。In the Configure SQL Server Agent Error Logs dialog box, specify the new location of the SQLAGENT.OUT file. 默认位置为:C:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Log\。The default location is C:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Log\.

更改数据库默认位置Change the database default location

  1. 从 SQL Server Management Studio 的对象资源管理器中,右键单击 SQL Server 所在服务器,然后单击 “属性”From SQL Server Management Studio, in Object Explorer, right-click the SQL Server server and click Properties.

  2. “服务器属性” 对话框中,选择 “数据库设置”In the Server Properties dialog box, select Database Settings.

  3. “数据库默认位置” 下,找到数据文件和日志文件的新位置。Under Database Default Locations, browse to the new location for both the data and log files.

  4. 先停止然后启动 SQL Server 服务以完成更改。Stop and start the SQL Server service to complete the change.

示例Examples

A.A. 移动 tempdb 数据库Moving the tempdb database

作为预先安排的重定位的一部分,下面的示例将 tempdb 数据和日志文件移动到一个新位置。The following example moves the tempdb data and log files to a new location as part of a planned relocation.

备注

由于每次启动 SQL ServerSQL Server 实例时都将重新创建 tempdb,所以不必实际移动数据和日志文件。Because tempdb is re-created each time the instance of SQL ServerSQL Server is started, you do not have to physically move the data and log files. 在步骤 3 中重新启动服务时,将在新位置中创建这些文件。The files are created in the new location when the service is restarted in step 3. 在重新启动服务之前,tempdb 将继续使用现有位置中的数据和日志文件。Until the service is restarted, tempdb continues to use the data and log files in existing location.

  1. 确定 tempdb 数据库的逻辑文件名称以及在磁盘上的当前位置。Determine the logical file names of the tempdb database and their current location on the disk.

    SELECT name, physical_name AS CurrentLocation  
    FROM sys.master_files  
    WHERE database_id = DB_ID(N'tempdb');  
    GO  
    
  2. 使用 ALTER DATABASE更改每个文件的位置。Change the location of each file by using 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  
    
  3. 停止再重新启动 SQL ServerSQL Server的实例。Stop and restart the instance of SQL ServerSQL Server.

  4. 验证文件更改。Verify the file change.

    SELECT name, physical_name AS CurrentLocation, state_desc  
    FROM sys.master_files  
    WHERE database_id = DB_ID(N'tempdb');  
    
  5. tempdb.mdftemplog.ldf 文件从其原始位置删除。Delete the tempdb.mdf and templog.ldf files from the original location.

另请参阅See Also

Resource 数据库 Resource Database
tempdb 数据库 tempdb Database
master 数据库 master Database
msdb 数据库 msdb Database
model 数据库 model Database
移动用户数据库 Move User Databases
移动数据库文件 Move Database Files
启动、停止、暂停、继续、重新启动数据库引擎、SQL Server 代理或 SQL Server Browser 服务 Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service
ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
重新生成系统数据库Rebuild System Databases