合 MSSQL移动数据库文件（分离和附加）

发布日期 2024年4月26日 · 已更新 2024年4月26日

0 199 1

Tags： MSSQL SQL Server 整理自官网分离和附加数据库文件

👉 本文共约26798个字，系统预计阅读时间或需101分钟。

安全性
分离数据库
备份、还原及分离
响应分离错误
附加数据库
附加数据库时的元数据更改
备份、还原及附加
将数据库附加到其他服务器实例
过程
使用分离和附加操作移动数据库
示例
开始之前
限制和局限
建议
使用分离和附加功能来升级数据库
示例
跟进：在升级 SQL Server 数据库之后
升级后的数据库兼容级别
管理已升级服务器实例上的元数据
服务主密钥和数据库主密钥加密从 3DES 更改为 AES
注意事项
计划的重定位过程
计划的磁盘维护的重定位
故障恢复过程
示例
移动系统数据库
后续操作：移动 msdb 系统数据库后
故障恢复过程
移动 master 数据库
移动资源数据库
后续操作：移动所有系统数据库后
更改 SQL Server 代理日志路径
更改数据库默认位置
示例
A. 移动 tempdb 数据库
限制和约束
先决条件
重新生成系统数据库
重新生成 SQL Server 实例的系统数据库
重新生成后的任务
重新生成 resource 数据库
重新生成 resource 系统数据库
创建新的 msdb 数据库
重新生成 tempdb 数据库
排查重新生成错误

数据库分离和附加 (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)。

响应分离错误

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

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

附加数据库

可以附加复制的或分离的 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 ，然后检查数据库中的代码，例如存储过程或其他用户定义代码。

过程

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

分离数据库。有关详细信息，请参阅分离数据库。
在 Windows 资源管理器或 Windows“命令提示符”窗口中，将分离的数据库文件和日志文件移至新位置。
即使打算创建新的日志文件，也应该移动日志文件。在某些情况下，重新附加数据库需要使用其现有的日志文件。因此，除非在不使用分离日志文件的情况下可以成功附加数据库，否则，请始终保留所有分离的日志文件。
备注
如果尝试在不指定日志文件的情况下附加数据库，则附加操作将会在日志文件的原始位置中查找文件。如果原始位置还有一份日志，则附加该日志。若要避免使用原始日志文件，请指定新日志文件的路径，或在日志文件复制到新位置之后，删除其原始副本。
附加复制的文件。有关详细信息，请参阅 Attach a Database。

示例

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

执行以下 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
使用您选择的方法，将数据库文件（AdventureWorks208R2_Data.mdf 和 AdventureWorks208R2_log）分别复制到：C:\MySQLServer\AdventureWorks208R2_Data.mdf 和 C:\MySQLServer\AdventureWorks208R2_Log.ldf。
重要
对于生产数据库，请将数据库和事务日志存放在不同的磁盘上。
若要通过网络将文件复制到远程计算机的磁盘上，请使用远程位置的通用命名约定 (UNC) 名称。 UNC 名称采用以下格式： \*服务器名称\共享名\路径\文件名*。将文件写入本地硬盘时，必须对 SQL Server 实例使用的用户帐户授予读写远程磁盘文件所需的相应权限。

通过执行以下 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

USE master;

CREATE DATABASE MyAdventureWorks

ON (FILENAME = 'C:\MySQLServer\AdventureWorks2022_Data.mdf'),

(FILENAME = 'C:\MySQLServer\AdventureWorks2022_Log.ldf')

FOR ATTACH;

在 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 ，然后检查数据库中的代码，例如存储过程或其他用户定义代码。

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

分离数据库。有关详细信息，请参阅分离数据库。
（可选）移动所分离的数据库文件和日志文件。
即使希望创建新的日志文件，也应该将日志文件与数据文件一起移动。在某些情况下，重新附加数据库需要使用其现有的日志文件。因此，除非在不使用分离日志文件的情况下可以成功附加数据库，否则，请始终保留所有分离的日志文件。
备注
如果尝试在不指定日志文件的情况下附加数据库，则附加操作将会在日志文件的原始位置中查找文件。如果该位置仍存在日志文件的原始副本，则附加该副本。若要避免使用原始日志文件，请指定新日志文件的路径，或在日志文件复制到新位置之后，删除其原始副本。
将复制的文件附加到 SQL Server 实例。有关详细信息，请参阅 Attach a Database。

示例

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

执行以下 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
使用您选择的方法，将数据文件和日志文件复制到新位置。
重要
对于生产数据库，最好将数据库和事务日志存放在不同的磁盘上。这些催生不同的 I/O 和文件增长需求，并且将其分隔开来被视为最佳做法。
若要通过网络将文件复制到远程计算机的磁盘上，请使用远程位置的通用命名约定 (UNC) 名称。 UNC 名称采用以下格式：\\Servername\Sharename\Path\Filename。将文件写入本地硬盘时，必须对 SQL Server 实例使用的用户帐户授予读写远程磁盘文件所需的相应权限。
通过执行以下 Transact-SQL 语句来附加移动的数据库及其日志（日志为可选项）：
SQL
USE master; GO CREATE DATABASE MyDatabase ON (FILENAME = 'C:\MySQLServer\MyDatabase.mdf'), (FILENAME = 'C:\MySQLServer\Database.ldf') FOR ATTACH; GO
1
2
3
4
5
6
7
USE master;
GO
CREATE DATABASE MyDatabase
   ON (FILENAME = 'C:\MySQLServer\MyDatabase.mdf'),
   (FILENAME = 'C:\MySQLServer\Database.ldf')
   FOR ATTACH;
GO
在 SQL Server Management Studio 中，新附加的数据库在对象资源管理器中不是立即可见的。若要查看数据库，请在对象资源管理器中，单击 “查看” ，再单击 “刷新”。在对象资源管理器中展开 “数据库” 节点后，新附加的数据库即显示在数据库列表中。

跟进：在升级 SQL Server 数据库之后

如果数据库具有全文检索，则升级过程将导入、重置或重新生成它们，具体取决于 upgrade_option 服务器属性的设置。如果将升级选项设置为“导入”(upgrade_option = 2) 或“重新生成”(upgrade_option = 0)，在升级过程中将无法使用全文检索。导入可能需要数小时，而重新生成所需的时间最多时可能十倍于此，具体取决于要编制索引的数据量。另请注意，如果将升级选项设置为“导入”，并且全文目录不可用，则会重新生成关联的全文索引。若要更改 upgrade_option 服务器属性的设置，请使用 sp_fulltext_service。

升级后的数据库兼容级别

升级后，数据库兼容性级别将保持在升级前的兼容性级别，除非新版本不支持以前的兼容性级别。在这种情况下，升级后的数据库兼容性级别将设置为支持的最低兼容性级别。

例如，如果附加兼容性级别为 90 的数据库，然后将其附加到 SQL Server 2019 (15.x) 的实例，在升级后，兼容性级别设置为 100，这是 SQL Server 2019 (15.x) 中支持的最低兼容性级别。有关详细信息，请参阅 ALTER DATABASE 兼容性级别 (Transact-SQL)。

管理已升级服务器实例上的元数据

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

服务主密钥和数据库主密钥加密从 3DES 更改为 AES

SQL Server 2012 (11.x) 及更高版本使用 AES 加密算法来保护服务主密钥 (SMK) 和数据库主密钥 (DMK)。 AES 是一种比早期版本中使用的 3DES 更新的加密算法。当数据库第一次附加或还原到新的 SQL Server 实例时，数据库主密钥（由服务主密钥加密）的副本尚未存储在服务器中。必须使用 OPEN MASTER KEY 语句解密数据库主密钥 (DMK)。一旦 DMK 解密后，通过使用 ALTER MASTER KEY REGENERATE 语句向服务器提供 DMK（使用服务主密钥 (SMK) 加密）的副本，即可拥有将来启用自动解密的选项。当数据库已从较早版本升级后，应重新生成 DMK 以使用更新的 AES 算法。有关重新生成 DMK 的详细信息，请参阅 ALTER MASTER KEY (Transact-SQL)。重新生成 DMK 密钥以升级到 AES 所需的时间取决于 DMK 保护的对象数。重新生成 DMK 密钥以升级到 AES 只在必需时执行一次，不影响将来作为密钥循环策略的一部分而重新生成的过程。

移动用户数据库

在 SQL Server 中，通过在 ALTER DATABASE 语句的 FILENAME 子句中指定新的文件位置，可以将用户数据库中的数据、日志和全文目录文件移动到新位置。此方法适用于在同一 SQL Server 实例中移动数据库文件。若要将数据库移动到另一个 SQL Server 实例或另一台服务器上，请使用备份和还原或分离和附加操作。

注意事项

将数据库移动到另一个服务器实例上时，若要为用户和应用程序提供一致的体验，您可能需要为数据库重新创建部分或全部元数据。有关详细信息，请参阅当数据库在其他服务器实例上可用时管理元数据 (SQL Server)。

SQL Server 数据库引擎的某些功能改变了数据库引擎在数据库文件中存储信息的方式。这些功能仅限于特定 SQL Server 版本。不能将包含这些功能的数据库移到不支持这些功能的 SQL Server 版本。使用 sys.dm_db_persisted_sku_features 动态管理视图可列出当前数据库中启用的所有特定于版本的功能。

本文中的过程需要数据库文件的逻辑名称。若要获取该名称，请在 sys.master_files 目录视图中查询名称列。

从 SQL Server 2008 R2 (10.50.x) 开始，全文目录已集成到数据库中，而不是存储在文件系统中。现在移动数据库时将自动移动全文目录。

备注

确保 SQL Server 数据库服务的服务帐户有权访问文件系统中的新文件位置。有关更多信息，请参阅配置数据库引擎访问的文件系统权限。

计划的重定位过程

若要将移动数据或日志文件作为计划的重定位的一部分，请执行下列步骤：

对于要移动的每个文件，请运行以下语句。
SQL
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
1
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
运行以下语句以使数据库脱机。
SQL
ALTER DATABASE database_name SET OFFLINE;
1
ALTER DATABASE database_name SET OFFLINE;
此操作需要对数据库有独占访问权限。如果打开了与数据库的另一个连接，则在所有连接都关闭之前，ALTER DATABASE 语句将被阻止。若要替代此行为，请使用 WITH 子句。例如，若要自动回滚并断开与数据库的所有其他连接，请使用：
SQL
ALTER DATABASE database_name SET OFFLINE WITH ROLLBACK IMMEDIATE;
1
ALTER DATABASE database_name SET OFFLINE WITH ROLLBACK IMMEDIATE;
将文件移动到新位置。
运行以下语句。
SQL
ALTER DATABASE database_name SET ONLINE;
1
ALTER DATABASE database_name SET ONLINE;
通过运行以下查询来验证文件更改。
SQL
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'<database_name>');
1
2
3
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');

计划的磁盘维护的重定位

若要将重定位文件作为计划的磁盘维护过程的一部分，请执行下列步骤：

对于要移动的每个文件，请运行以下语句。
SQL
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' );
1
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' );
停止 SQL Server 实例或关闭系统以执行维护。有关详细信息，请参阅启动、停止、暂停、继续、重启 SQL Server 服务。
将文件移动到新位置。
重启 SQL Server 实例或服务器。有关详细信息，请参阅启动、停止、暂停、继续、重新启动数据库引擎、SQL Server 代理或 SQL Server Browser 服务
通过运行以下查询来验证文件更改。
SQL
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'<database_name>');
1
2
3
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');

故障恢复过程

如果由于硬件故障而必须移动文件，则请执行下列步骤，将文件重新定位到一个新位置。

重要

如果数据库无法启动，即处于可疑模式下或处于未恢复状态，则只有 sysadmin 固定角色的成员才可以移动该文件。

如果启动了 SQL Server 实例，则将其停止。
通过在命令提示符下输入下列命令之一，在仅 master 恢复模式下启动 SQL Server 实例。
- 对于默认的 (MSSQLSERVER) 实例，请运行以下命令。
  控制台
  NET START MSSQLSERVER /f /T3608
  1
  NET START MSSQLSERVER /f /T3608
- 对于命名实例，请运行以下命令。
  控制台
  NET START MSSQL$instancename /f /T3608
  1
  NET START MSSQL$instancename /f /T3608
有关详细信息，请参阅启动、停止、暂停、继续、重启 SQL Server 服务。
对于要移动的每个文件，请使用 sqlcmd 命令或 SQL Server Management Studio 运行以下语句。
SQL
ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' );
1
ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' );
有关如何使用 sqlcmd 实用工具的详细信息，请参阅使用 sqlcmd 实用工具。
退出 sqlcmd 实用工具或 SQL Server Management Studio。
停止 SQL Server 实例。
将文件移动到新位置。
启动 SQL Server 实例。例如，运行 NET START MSSQLSERVER。
通过运行以下查询来验证文件更改。
SQL
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'<database_name>');
1
2
3
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');

示例

下面的示例将 AdventureWorks2022 日志文件移动到一个新位置，作为计划的重定位的一部分。

SQL

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'AdventureWorks2022')  
    AND type_desc = N'LOG';  
GO  
ALTER DATABASE AdventureWorks2022 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 AdventureWorks2022   
    MODIFY FILE ( NAME = AdventureWorks2022_Log,   
                  FILENAME = 'C:\NewLoc\AdventureWorks2022_Log.ldf');  
GO  
ALTER DATABASE AdventureWorks2022 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'AdventureWorks2022')  
    AND type_desc = N'LOG';

USE master;

-- Return the logical file name.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'AdventureWorks2022')

AND type_desc = N'LOG';

ALTER DATABASE AdventureWorks2022 SET OFFLINE;

-- 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 AdventureWorks2022

MODIFY FILE ( NAME = AdventureWorks2022_Log,

FILENAME = 'C:\NewLoc\AdventureWorks2022_Log.ldf');

ALTER DATABASE AdventureWorks2022 SET ONLINE;

--Verify the new location.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'AdventureWorks2022')

AND type_desc = N'LOG';

移动系统数据库

本文说明如何在 SQL Server 中移动系统数据库。移动系统数据库在下列情况下可能很有用：

故障恢复。例如，数据库处于可疑模式下或因硬件故障而关闭。
预先安排的重定位。
为预定的磁盘维护操作而进行的重定位。

下列过程适用于在同一 SQL Server 实例内移动数据库文件。若要将数据库移动另一个 SQL Server 实例中或另一台服务器上，请使用备份和还原操作。

本文中的过程需要数据库文件的逻辑名称。若要获取该名称，请在 sys.master_files 目录视图中查询名称列。

重要

如果移动系统数据库并随后重新生成 master 数据库，则必须再次移动系统数据库，因为重新生成操作会将所有系统数据库安装到其默认位置。

移动系统数据库

若要将移动系统数据库数据或日志文件的操作作为预先安排的重定位或预定的维护操作的一部分，请按照下列步骤操作。其中包括 model、msdb 和 tempdb 系统数据库。

重要

此过程适用于除 master 和 Resource 数据库以外的所有系统数据库。有关移动 master 数据库的步骤，请参阅本文的后半部分。无法移动 Resource 数据库。

通过查看 sys.master_files 目录视图，记录要移动的数据库文件的现有位置。
验证 SQL Server 数据库引擎的服务帐户是否对文件的新位置具有完全权限。有关详细信息，请参阅配置 Windows 服务帐户和权限预览版本升级问题的解答。如果数据库引擎服务帐户无法控制其新位置中的文件，则 SQL Server 实例将不会启动。
对于要移动的每个数据库文件，请运行以下语句。
SQL
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' );
1
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' );
在重新启动服务之前，数据库将继续使用现有位置中的数据和日志文件。
停止 SQL Server 实例以执行维护。有关详细信息，请参阅启动、停止、暂停、继续、重启 SQL Server 服务。
将数据库文件或文件复制到新位置。请注意，这不是 tempdb 系统数据库的必要步骤，将自动在新位置创建这些文件。
重启 SQL Server 实例或服务器。有关详细信息，请参阅启动、停止、暂停、继续、重启 SQL Server 服务。
通过运行以下查询来验证文件更改。系统数据库应报告新的物理文件位置。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！
验证码：
获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。
相关文章
打赏赞(1)分享

标签： MSSQL SQL Server 整理自官网分离和附加数据库文件

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。

一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 MSSQL移动数据库文件（分离和附加）

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

安全性

分离数据库

备份、还原及分离

响应分离错误

附加数据库

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

备份、还原及附加

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

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

过程

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

示例

离和附加来升级数据库

开始之前

限制和局限

建议

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

示例

跟进：在升级 SQL Server 数据库之后

升级后的数据库兼容级别

管理已升级服务器实例上的元数据

服务主密钥和数据库主密钥加密从 3DES 更改为 AES

移动用户数据库

注意事项

计划的重定位过程

计划的磁盘维护的重定位

故障恢复过程

示例

移动系统数据库

移动系统数据库

相关文章

您可能还喜欢...

Oracle、SQL Server、PG、GP、MySQL等数据库批量kill杀会话汇总

MSSQL删除tempdb的数据文件

MSSQL中的大小写和排序规则问题

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复