数据库分离和附加 (SQL Server)

可以分离数据库的数据和事务日志文件，然后将其重新附加到同一或其他 SQL Server 实例。 如果要将数据库更改到同一计算机上的不同 SQL Server 实例或要移动数据库，分离和附加数据库会很有用。

安全性

文件访问权限可在很多数据库操作过程中设置，其中包括分离或附加数据库。

重要

不建议附加或还原来自未知或不受信任的源的数据库。 此类数据库可能包含恶意代码，这些代码可能会执行非预期的 Transact-SQL 代码，或者通过修改架构或物理数据库结构导致错误。
使用来自未知源或不可信源的数据库前，请在非生产服务器上针对数据库运行 DBCC CHECKDB ，然后检查数据库中的代码，例如存储过程或其他用户定义代码。

分离数据库

分离数据库是指将数据库从 SQL Server 实例中移除，但会完整保留数据库及其数据文件和事务日志文件。 然后可以使用这些文件将数据库附加到任何 SQL Server 实例，包括分离该数据库的服务器。

如果存在下列任何情况，则不能分离数据库：

• 已复制并发布数据库。 如果进行复制，则数据库必须是未发布的。 必须通过运行 sp_replicationdboption禁用发布后，才能分离数据库。

  备注

  如果无法使用 sp_replicationdboption，可以通过运行 sp_removedbreplication删除复制。

• 数据库中存在数据库快照。

  必须首先删除所有数据库快照，然后才能分离数据库。 有关详细信息，请参阅 删除数据库快照 (Transact-SQL)。

  备注

  不能分离或附加数据库快照。

• 数据库是 Always On 可用性组的一部分。

  在将数据库从可用性组中删除之前，无法分离该数据库。 有关详细信息，请参阅从 Always On 可用性组中删除主数据库。

• 该数据库正在某个数据库镜像会话中进行镜像。

  除非终止该会话，否则无法分离该数据库。 有关详细信息，请参阅删除数据库镜像 (SQL Server)。

• 数据库处于可疑状态。 无法分离可疑数据库；必须将数据库设为紧急模式，才能对其进行分离。 有关如何将数据库置于紧急模式下的详细信息，请参阅 ALTER DATABASE (Transact-SQL)。

• 数据库为系统数据库。

备份、还原及分离

分离只读数据库将会丢失有关差异备份的差异基准的信息。 有关详细信息，请参阅差异备份 (SQL Server)。

响应分离错误

分离数据库时生成的错误会阻止完全关闭数据库和重新生成事务日志。 收到错误消息后，请执行下列更正操作：

1. 重新附加与数据库关联的所有文件，而不仅仅是主文件。
2. 解决导致生成错误消息的问题。
3. 再次分离数据库。

附加数据库

可以附加复制的或分离的 SQL Server 数据库。 将包含全文目录文件的 SQL Server 2005 (9.x) 数据库附加到 SQL Server 服务器实例时，与在 SQL Server 2005 (9.x) 中一样，目录文件与其他数据库文件一起从旧位置附加。 有关详细信息，请参阅 全文搜索升级。

附加数据库时，所有数据文件（MDF 文件和 NDF 文件）都必须可用。 如果任何数据文件的路径不同于首次创建数据库或上次附加数据库时的路径，则必须指定文件的当前路径。

备注

如果附加的主数据文件是只读的，则数据库引擎假定数据库也是只读的。

当加密的数据库首次附加到 SQL Server 实例时，数据库所有者必须通过执行下面的语句打开数据库的主密钥：OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'。 建议通过执行下面的语句对主密钥启用自动解密：ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY。 有关详细信息，请参阅 CREATE MASTER KEY (Transact-SQL) 和 ALTER MASTER KEY (Transact-SQL)。

附加日志文件的要求在某些方面取决于数据库是读写的还是只读的，如下所示：

• 对于读写数据库，通常可以附加新位置中的日志文件。 不过，在某些情况下，重新附加数据库需要使用其现有的日志文件。 因此，请务必保留所有分离的日志文件，直到在不需要这些日志文件的情况下成功附加了数据库。

  如果读写数据库具有单个日志文件，并且您没有为该日志文件指定新位置，附加操作将在旧位置中查找该文件。 如果找到了旧日志文件，则无论数据库上次是否完全关闭，都将使用该文件。 但是，如果未找到旧文件日志，数据库上次是完全关闭且现在没有活动日志链，则附加操作将尝试为数据库创建新的日志文件。

• 如果附加的主数据文件是只读的，则数据库引擎假定数据库也是只读的。 对于只读数据库，日志文件在数据库主文件中指定的位置上必须可用。 由于 SQL Server 无法更新存储在主文件中的日志位置，因此无法生成新的日志文件。

附加数据库时的元数据更改

分离再重新附加只读数据库后，会丢失有关当前差异基准的备份信息。 “差异基准” 是数据库或其文件或文件组子集中所有数据的最新完整备份。 如果没有基准备份信息， master 数据库会变得与只读数据库不同步，这样之后进行的差异备份可能会产生意外结果。 因此，如果对只读数据库使用差异备份，在重新附加数据库后，应通过进行完整备份来建立新的差异基准。 有关差异备份的信息，请参阅 差异备份 (SQL Server)。

附加时，数据库会启动。 通常，附加数据库时会将数据库重置为它分离或复制时的状态。 但是，附加和分离操作都会禁用数据库的跨数据库所有权链接。 有关如何启用链接的详细信息，请参阅 cross db ownership chaining 服务器配置选项。

重要

为安全起见，，默认情况下，每当附加数据库时，is_broker_enabled、is_honoor_broker_priority_on 和 is_trustworthy_on 的选项均设置为 OFF。 有关如何开启这些选项设置的详细信息，请参阅 ALTER DATABASE (Transact-SQL)。 有关元数据的详细信息，请参阅使数据库在其他服务器上可用时管理元数据。

备份、还原及附加

与任何完全或部分脱机的数据库一样，不能附加正在还原文件的数据库。 如果停止了还原顺序，则可以附加数据库。 然后，可以重新启动还原顺序。

将数据库附加到其他服务器实例

重要

无法在早期版本的 SQL Server 中附加由较新版本的 SQL Server 创建的数据库。 这将阻止数据库被旧版本的数据库引擎物理使用。 但是，这与元数据状态相关且不会影响数据库兼容性级别。 有关详细信息，请参阅 ALTER DATABASE 兼容性级别 (Transact-SQL)。

将数据库附加到其他服务器实例时，为了给用户和应用程序提供一致的体验，您最好在其他服务器实例上为数据库重新创建部分或全部元数据（例如登录名和作业）。 有关详细信息，请参阅 当数据库在其他服务器实例上可用时管理元数据 (SQL Server)。

通过分离和附加来移动数据库 (Transact-SQL)

使用分本主题说明如何在 SQL Server 中将分离的数据库移至其他位置，并将其重新附加到相同或不同的服务器实例。 但是，我们建议您使用 ALTER DATABASE 计划重定位过程（而不使用分离和附加操作）移动数据库。 有关详细信息，请参阅 Move User Databases。

重要

不建议附加或还原来自未知或不受信任的源的数据库。 此类数据库可能包含恶意代码，这些代码可能会执行非预期的 Transact-SQL 代码，或者通过修改架构或物理数据库结构导致错误。 使用来自未知源或不可信源的数据库前，请在非生产服务器上针对数据库运行 DBCC CHECKDB ，然后检查数据库中的代码，例如存储过程或其他用户定义代码。

过程

使用分离和附加操作移动数据库

1. 分离数据库。 有关详细信息，请参阅 分离数据库。

2. 在 Windows 资源管理器或 Windows“命令提示符”窗口中，将分离的数据库文件和日志文件移至新位置。

   即使打算创建新的日志文件，也应该移动日志文件。 在某些情况下，重新附加数据库需要使用其现有的日志文件。 因此，除非在不使用分离日志文件的情况下可以成功附加数据库，否则，请始终保留所有分离的日志文件。

   备注

   如果尝试在不指定日志文件的情况下附加数据库，则附加操作将会在日志文件的原始位置中查找文件。 如果原始位置还有一份日志，则附加该日志。 若要避免使用原始日志文件，请指定新日志文件的路径，或在日志文件复制到新位置之后，删除其原始副本。

3. 附加复制的文件。 有关详细信息，请参阅 Attach a Database。

示例

以下示例创建名为 MyAdventureWorks 的 AdventureWorks2022 数据库副本。 Transact-SQL 语句在与该服务器实例（附加该数据库副本）连接的查询编辑器窗口中执行。

1. 执行以下 Transact-SQL 语句以分离 AdventureWorks2022 数据库：

   SQL

   USE master; GO EXEC sp_detach_db @dbname = N'AdventureWorks2022'; GO

   1 2 3 4

   USE master;   GO   EXEC sp_detach_db @dbname = N'AdventureWorks2022';   GO

2. 使用您选择的方法，将数据库文件（AdventureWorks208R2_Data.mdf 和 AdventureWorks208R2_log）分别复制到：C:\MySQLServer\AdventureWorks208R2_Data.mdf 和 C:\MySQLServer\AdventureWorks208R2_Log.ldf。

   重要

   对于生产数据库，请将数据库和事务日志存放在不同的磁盘上。

   若要通过网络将文件复制到远程计算机的磁盘上，请使用远程位置的通用命名约定 (UNC) 名称。 UNC 名称采用以下格式： \*服务器名称\共享名\路径\文件名*。 将文件写入本地硬盘时，必须对 SQL Server 实例使用的用户帐户授予读写远程磁盘文件所需的相应权限。

3. 通过执行以下 Transact-SQL 语句来附加移动的数据库及其日志（日志为可选项）：

   SQL

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

   1 2 3 4 5 6 7

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

   在 SQL Server Management Studio 中，新附加的数据库在对象资源管理器中不是立即可见的。 若要查看数据库，请在对象资源管理器中，单击 “查看” ，再单击 “刷新”。 在对象资源管理器中展开 “数据库” 节点后，新附加的数据库即显示在数据库列表中。

离和附加来升级数据库

本主题说明如何使用分离和附加操作在 SQL Server 中升级数据库。 在附加到 SQL Server 后，数据库将立即变为可用，然后会自动进行升级。 这可以防止数据库与旧版本的数据库引擎一起使用。 但是，元数据升级不会影响数据库的数据库兼容性级别设置。 有关详细信息，请参阅本主题后面的升级后的数据库兼容性级别。

在本主题中

• 开始之前：

  限制和局限

  建议

• 升级 SQL Server 数据库：

  使用分离和附加操作

• 跟进：

  升级 SQL Server 数据库之后

开始之前

限制和局限

• 不能附加系统数据库。
• 附加和分离操作可以通过将数据库的 cross db ownership chaining 选项设置为 0 来禁用数据库的跨数据库所有权链接。 有关启用链接的详细信息，请参阅 cross db ownership chaining 服务器配置选项。
• 附加复制的而不是分离的复制数据库时：
  • 如果将该数据库附加到同一服务器实例的升级版本中，则必须在附加操作完成后执行 sp_vupgrade_replication 来升级复制数据库。 有关详细信息，请参阅 sp_vupgrade_replication (Transact-SQL)。
  • 如果将该数据库附加到不同的服务器实例中（不考虑版本），则必须在附加操作完成后执行 sp_removedbreplication 来删除复制数据库。 有关详细信息，请参阅 sp_removedbreplication (Transact-SQL)。

建议

不建议附加或还原来自未知或不受信任的源的数据库。 此类数据库可能包含恶意代码，这些代码可能会执行非预期的 Transact-SQL 代码，或者通过修改架构或物理数据库结构导致错误。 使用来自未知源或不可信源的数据库前，请在非生产服务器上针对数据库运行 DBCC CHECKDB ，然后检查数据库中的代码，例如存储过程或其他用户定义代码。

使用分离和附加功能来升级数据库

1. 分离数据库。 有关详细信息，请参阅 分离数据库。

2. （可选）移动所分离的数据库文件和日志文件。

   即使希望创建新的日志文件，也应该将日志文件与数据文件一起移动。 在某些情况下，重新附加数据库需要使用其现有的日志文件。 因此，除非在不使用分离日志文件的情况下可以成功附加数据库，否则，请始终保留所有分离的日志文件。

   备注

   如果尝试在不指定日志文件的情况下附加数据库，则附加操作将会在日志文件的原始位置中查找文件。 如果该位置仍存在日志文件的原始副本，则附加该副本。 若要避免使用原始日志文件，请指定新日志文件的路径，或在日志文件复制到新位置之后，删除其原始副本。

3. 将复制的文件附加到 SQL Server 实例。 有关详细信息，请参阅 Attach a Database。

示例

以下实例升级以前版本的 SQL Server 的数据库副本。 Transact-SQL 语句在与该服务器实例（附加该数据库副本）连接的查询编辑器窗口中执行。

1. 执行以下 Transact-SQL 语句以分离数据库：

   SQL

   USE master; GO EXEC sp_detach_db @dbname = N'MyDatabase'; GO

   1 2 3 4

   USE master;   GO   EXEC sp_detach_db @dbname = N'MyDatabase';   GO

2. 使用您选择的方法，将数据文件和日志文件复制到新位置。

   重要

   对于生产数据库，最好将数据库和事务日志存放在不同的磁盘上。 这些催生不同的 I/O 和文件增长需求，并且将其分隔开来被视为最佳做法。

   本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
   后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

   验证码：

   请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

   相关文章

   • MSSQL的创建索引占用的内存
   • MSSQL 2014新功能之缓存池扩展BPE (Buffer Pool Extension)
   • MSSQL中的锁和阻塞
   • MSSQL中的数据库兼容性级别
   • MSSQL中的透明数据加密 (TDE)介绍
   • MSSQL分离和附加数据库后变为只读
   • MSSQL无日志文件附加数据库
   • 排查SQL Server中的内存不足或内存不足问题
   • 排查 IO 问题导致的SQL Server性能缓慢问题
   • MSSQL中创建索引、在线创建索引、索引并行、创建索引内存分配
   • Windows的资源监视器中进程对磁盘的响应时间多少毫秒即为延迟比较厉害影响磁盘性能IO
   • MSSQL中的的最大容量规范
   • MSSQL扩展事件之system_health会话
   • 提升 SQL Server 的优先级
   • MSSQL 2017的自动优化
   • MSSQL 2016使用查询存储来监视性能
   • MSSQL中的索引填充因子（fill factor）
   • MSSQL服务主密钥
   • MSSQL查看事务日志的虚拟日志文件（VLF）信息sys.dm_db_log_info
   • datediff 函数导致溢出。用于分隔两个日期 时间实例的日期部分的数字太大。请尝试使用日期部分精度较低的 datediff
   打赏赞(1)分享