通过分离和附加来移动数据库 (Transact-SQL)Move a Database Using Detach and Attach (Transact-SQL)

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

本主题说明如何在 SQL Server 2017SQL Server 2017中将分离的数据库移至其他位置,并将其重新附加到相同或不同的服务器实例。This topic describes how to move a detached database to another location and re-attach it to the same or a different server instance in SQL Server 2017SQL Server 2017. 但是,我们建议您使用 ALTER DATABASE 计划重定位过程(而不使用分离和附加操作)移动数据库。However, we recommend that you move databases by using the ALTER DATABASE planned relocation procedure, instead of using detach and attach. 有关详细信息,请参阅 Move User DatabasesFor more information, see Move User Databases.

重要

建议您不要附加或还原来自未知或不可信源的数据库。We recommend that you do not attach or restore databases from unknown or untrusted sources. 此类数据库可能包含恶意代码,这些代码可能会执行非预期的 Transact-SQLTransact-SQL 代码,或者通过修改架构或物理数据库结构导致错误。Such databases could contain malicious code that might execute unintended Transact-SQLTransact-SQL code or cause errors by modifying the schema or the physical database structure. 使用来自未知源或不可信源的数据库前,请在非生产服务器上针对数据库运行 DBCC CHECKDB ,然后检查数据库中的代码,例如存储过程或其他用户定义代码。Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

过程Procedure

使用分离和附加操作移动数据库To move a database by using detach and attach

  1. 分离数据库。Detach the database. 有关详细信息,请参阅 分离数据库For more information, see Detach a Database.

  2. 在 Windows 资源管理器或 Windows“命令提示符”窗口中,将分离的数据库文件和日志文件移至新位置。In a Windows Explorer or Windows Command Prompt window, move the detached database file or files and log file or files to the new location.

    即使打算创建新的日志文件,也应该移动日志文件。You should move the log files even if you intend to create new log files. 在某些情况下,重新附加数据库需要使用其现有的日志文件。In some cases, reattaching a database requires its existing log files. 因此,除非在不使用分离日志文件的情况下可以成功附加数据库,否则,请始终保留所有分离的日志文件。Therefore, always keep all the detached log files until the database has been successfully attached without them.

    备注

    如果尝试在不指定日志文件的情况下附加数据库,则附加操作将会在日志文件的原始位置中查找文件。If you try to attach the database without specifying the log file, the attach operation will look for the log file in its original location. 如果原始位置还有一份日志,则附加该日志。If a copy of the log still exists in the original location, that copy is attached. 若要避免使用原始日志文件,请指定新日志文件的路径,或在日志文件复制到新位置之后,删除其原始副本。To avoid using the original log file, either specify the path of the new log file or remove the original copy of the log file (after copying it to the new location).

  3. 附加复制的文件。Attach the copied files. 有关详细信息,请参阅 Attach a DatabaseFor more information, see Attach a Database.

示例Example

以下示例创建名为 AdventureWorks2012AdventureWorks2012MyAdventureWorks数据库副本。The following example creates a copy of the AdventureWorks2012AdventureWorks2012 database named MyAdventureWorks. Transact-SQLTransact-SQL 语句在与该服务器实例(附加该数据库副本)连接的查询编辑器窗口中执行。The Transact-SQLTransact-SQL statements are executed in a Query Editor window that is connected to the server instance to which is attached.

  1. 执行以下 AdventureWorks2012AdventureWorks2012 语句以分离 Transact-SQLTransact-SQL 数据库:Detach the AdventureWorks2012AdventureWorks2012 database by executing the following Transact-SQLTransact-SQL statements:

    USE master;  
    GO  
    EXEC sp_detach_db @dbname = N'AdventureWorks2012';  
    GO  
    
  2. 使用所选方法,将数据库文件(AdventureWorks208R2_Data.mdf 和 AdventureWorks208R2_log)复制到:C:\MySQLServer\AdventureWorks208R2_Data.mdf 和 C:\MySQLServer\AdventureWorks208R2_Log.ldf。Using the method of your choice, copy the database files (AdventureWorks208R2_Data.mdf and AdventureWorks208R2_log) to: C:\MySQLServer\AdventureWorks208R2_Data.mdf and C:\MySQLServer\AdventureWorks208R2_Log.ldf, respectively.

    重要

    对于生产数据库,请将数据库和事务日志存放在不同的磁盘上。For a production database, place the database and transaction log on separate disks.

    若要通过网络将文件复制到远程计算机的磁盘上,请使用远程位置的通用命名约定 (UNC) 名称。To copy files over the network to a disk on a remote computer, use the universal naming convention (UNC) name of the remote location. UNC 名称采用以下格式: \\ 服务器名称 \ 共享名 \ 路径 \ 文件名A UNC name takes the form \\Servername\Sharename\Path\Filename. 将文件写入本地硬盘时,必须对 SQL ServerSQL Server实例使用的用户帐户授予读写远程磁盘文件所需的相应权限。As with writing files to the local hard disk, the appropriate permissions that are required to read or write to a file on the remote disk must be granted to the user account used by the instance of SQL ServerSQL Server.

  3. 通过执行以下 Transact-SQLTransact-SQL 语句来附加移动的数据库及其日志(可选):Attach the moved database and, optionally, its log by executing the following Transact-SQLTransact-SQL statements:

    USE master;  
    GO  
    CREATE DATABASE MyAdventureWorks   
        ON (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Data.mdf'),  
        (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Log.ldf')  
        FOR ATTACH;  
    GO  
    

    SQL Server Management StudioSQL Server Management Studio中,新附加的数据库在对象资源管理器中不是立即可见的。In SQL Server Management StudioSQL Server Management Studio, a newly attached database is not immediately visible in Object Explorer. 若要查看数据库,请在对象资源管理器中,单击 “查看” ,再单击 “刷新”To view the database, in Object Explorer, click View, and then Refresh. 在对象资源管理器中展开 “数据库” 节点后,新附加的数据库即显示在数据库列表中。When the Databases node is expanded in Object Explorer, the newly attached database now appears in the list of databases.

另请参阅See Also

数据库分离和附加 (SQL Server)Database Detach and Attach (SQL Server)