移动用户数据库Move User Databases

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

SQL ServerSQL Server中,通过在 ALTER DATABASE 语句的 FILENAME 子句中指定新的文件位置,可以将用户数据库中的数据、日志和全文目录文件移动到新位置。In SQL ServerSQL Server, you can move the data, log, and full-text catalog files of a user database to a new location by specifying the new file location in the FILENAME clause of the ALTER DATABASE statement. 此方法适用于在同一 SQL ServerSQL Server实例中移动数据库文件。This method applies to moving database files within the same instance SQL ServerSQL Server. 若要将数据库移动到另一个 SQL ServerSQL Server 实例或另一台服务器上,请使用 备份和还原分离和附加操作To move a database to another instance of SQL ServerSQL Server or to another server, use backup and restore or detach and attach operations.

注意事项Considerations

将数据库移动到另一个服务器实例上时,若要为用户和应用程序提供一致的体验,您可能需要为数据库重新创建部分或全部元数据。When you move a database onto another server instance, to provide a consistent experience to users and applications, you might have to re-create some or all the metadata for the database. 有关详细信息,请参阅 当数据库在其他服务器实例上可用时管理元数据 (SQL Server)For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server).

SQL Server 数据库引擎SQL Server Database Engine的某些功能改变了数据库引擎Database Engine在数据库文件中存储信息的方式。Some features of the SQL Server 数据库引擎SQL Server Database Engine change the way that the 数据库引擎Database Engine stores information in the database files. 这些功能仅限于特定 SQL ServerSQL Server版本。These features are restricted to specific editions of SQL ServerSQL Server. 不能将包含这些功能的数据库移到不支持这些功能的 SQL ServerSQL Server 版本。A database that contains these features cannot be moved to an edition of SQL ServerSQL Server that does not support them. 使用 sys.dm_db_persisted_sku_features 动态管理视图可列出当前数据库中启用的所有特定于版本的功能。Use the sys.dm_db_persisted_sku_features dynamic management view to list all edition-specific features that are enabled in the current database.

本主题中的过程需要数据库文件的逻辑名称。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.

SQL Server 2008 R2SQL Server 2008 R2开始,全文目录已集成到数据库中,而不是存储在文件系统中。Starting with SQL Server 2008 R2SQL Server 2008 R2, full-text catalogs are integrated into the database rather than being stored in the file system. 现在移动数据库时将自动移动全文目录。The full-text catalogs now move automatically when you move a database.

计划的重定位过程Planned Relocation Procedure

若要将移动数据或日志文件作为计划的重定位的一部分,请执行下列步骤:To move a data or log file as part of a planned relocation, follow these steps:

  1. 运行以下语句。Run the following statement.

    ALTER DATABASE database_name SET OFFLINE;  
    
  2. 将文件移动到新位置。Move the file or files to the new location.

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

    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );  
    
  4. 运行以下语句。Run the following statement.

    ALTER DATABASE database_name SET ONLINE;  
    
  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>');  
    

计划的磁盘维护的重定位Relocation for Scheduled Disk Maintenance

若要将重定位文件作为计划的磁盘维护过程的一部分,请执行下列步骤:To relocate a file as part of a scheduled disk maintenance process, follow these steps:

  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 代理或 SQL Server Browser 服务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>');  
    

故障恢复过程Failure Recovery Procedure

如果由于硬件故障而必须移动文件,则请执行下列步骤,将文件重新定位到一个新位置。If a file must be moved because of a hardware failure, use the following steps to relocate the file to a new location.

重要

如果数据库无法启动,即处于可疑模式下或处于未恢复状态,则只有 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.

    • 对于默认的 (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 how to use 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.

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

  7. 启动 SQL ServerSQL Server实例。Start 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>');  
    

示例Examples

下面的示例将 AdventureWorks2012AdventureWorks2012 日志文件移动到一个新位置,作为计划的重定位的一部分。The following example moves the AdventureWorks2012AdventureWorks2012 log file to a new location as part of a planned relocation.

USE master;  
GO  
-- Return the logical file name.  
SELECT name, physical_name AS CurrentLocation, state_desc  
FROM sys.master_files  
WHERE database_id = DB_ID(N'AdventureWorks2012')  
    AND type_desc = N'LOG';  
GO  
ALTER DATABASE AdventureWorks2012 SET OFFLINE;  
GO  
-- Physically move the file to a new location.  
-- In the following statement, modify the path specified in FILENAME to  
-- the new location of the file on your server.  
ALTER DATABASE AdventureWorks2012   
    MODIFY FILE ( NAME = AdventureWorks2012_Log,   
                  FILENAME = 'C:\NewLoc\AdventureWorks2012_Log.ldf');  
GO  
ALTER DATABASE AdventureWorks2012 SET ONLINE;  
GO  
--Verify the new location.  
SELECT name, physical_name AS CurrentLocation, state_desc  
FROM sys.master_files  
WHERE database_id = DB_ID(N'AdventureWorks2012')  
    AND type_desc = N'LOG';  

另请参阅See Also

ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
CREATE DATABASE (SQL Server Transact-SQL) CREATE DATABASE (SQL Server Transact-SQL)
数据库分离和附加 (SQL Server) Database Detach and Attach (SQL Server)
移动系统数据库 Move System Databases
移动数据库文件 Move Database Files
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
启动、停止、暂停、继续、重新启动数据库引擎、SQL Server 代理或 SQL Server Browser 服务Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service