如何移动SQL Server数据库文件？

#1 楼

您不必停止SQL Server服务即可移动数据库文件，但必须使特定的数据库脱机。这是因为在访问文件时无法移动文件，并且使数据库脱机会阻止SQL Server应用程序使用文件。

移动文件的过程非常简单。分离/连接已经描述过了，但是并没有那么复杂。

使用ALTER DATABASE命令更改文件位置：

USE master; --do this all from the master
ALTER DATABASE foo
MODIFY FILE (name='DB_Data1'
             ,filename='X:\NewDBFile\DB_Data1.mdf'); --Filename is new location

注意，则无需在此命令中声明旧位置。更改此路径不会立即生效，但将在下次数据库启动时使用。

使数据库脱机

（我使用WITH ROLLBACK IMMEDIATE将所有人踢出并回滚所有当前打开的事务）

ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;

将文件移动/复制到新位置

只需使用您喜欢的方法将文件复制（单击'n Drag，XCopy，Copy-Item，Robocopy）

使数据库联机

ALTER DATABASE foo SET ONLINE;

您可以更详细地看到此内容在这里。

#2 楼

MDF和LDF文件受保护，并且在数据库处于联机状态时无法移动。

如果您不介意停止数据库的运行，则可以先进行DETACH的操作，先移动文件再进行ATTACH的操作。


右键单击数据库名称
选择Properties

转到Files选项卡

进行记录MDF和LDF文件的Path和FileName。如果您不想结束搜索丢失的文件，则此步骤很重要...
右键单击数据库名称
选择Tasks -> Detach

将文件移动到您要删除的文件中想要
右键单击服务器的Databases节点
选择Attach

单击Add按钮
指向新位置
单击OK
/>

您现在应该可以了。有关DETACH-ATTACH进程的信息，可以在这里找到。

在有关DETACH-ATTACH的链接中，建议将ALTER DATABASE语句保持在SQL Server的同一实例上。在移动用户数据库中有更多参考。

如果要在移动时保持运行，请执行BACKUP-RESTORE。在还原过程中，您可以定义数据库文件的新位置。

#3 楼

要移动系统数据库文件，请执行以下步骤：


以sa用户身份在SSMS中登录
为安全起见，请备份用户创建的数据库。
杀死所有会话从SSMS连接到服务器。

执行以下命令以检查系统数据库的当前文件位置：

USE master; SELECT * FROM sys.master_files;


标识路径并记下文件的当前路径。



使用TSQL更改除master以外的所有数据库的文件路径：

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


例如：

ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdev
, FILENAME = "DestinationPath\tempdb.mdf");

ALTER DATABASE tempdb
MODIFY FILE ( NAME = templog
, FILENAME = "DestinationPath\templog.ldf");

ALTER DATABASE model
MODIFY FILE ( NAME = modeldev
, FILENAME = "DestinationPath\model.mdf");

ALTER DATABASE model
MODIFY FILE ( NAME = modellog
, FILENAME = "DestinationPath\modellog.ldf");

ALTER DATABASE msdb
    MODIFY FILE ( NAME = MSDBData
, FILENAME = "DestinationPath\msdbdata.mdf");

ALTER DATABASE msdb
    MODIFY FILE ( NAME = MSDBLog
, FILENAME = "DestinationPath\msdblog.ldf");

现在文件位置已更改。

请确保移动ldf和mdf文件



在SSMS中，右键单击服务器，然后选择属性。内部属性转到“数据库设置”。更改数据库默认位置的数据并登录到目标路径。从服务器注销。

例如：将C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\更改为E:\projects\DataBaseFiles\MSSQL\DATA\

停止SQL Server的实例。

将文件复制到Windows Server 2003。新位置。使用Robocopy移动文件，以便将访问权限复制到目标文件夹。打开cmd并以管理员身份运行，并使用以下命令：

robocopy / sec sourceFolder destinationFolder


最好转到源位置以运行命令。删除复制的系统数据库文件以外的其他文件。例如：

 robocopy /sec .\DATA E:\projects\DataBaseFiles\MSSQL\DATA\

（这里我们将所有系统数据库文件移动到新位置。）


从“开始”菜单，指向“所有程序”，指向“ Microsoft SQL Server”，指向“配置工具”，然后单击“ SQL Server配置管理器”。

在SQL Server配置管理器中执行以下步骤：

在“ SQL Server服务”节点中，右键单击SQL Server实例（例如，SQL Server（MSSQLSERVER）），然后选择“属性”。在“ SQL Server（instance_name）属性”对话框中，单击“启动参数”选项卡。在“现有参数”框中，选择-d参数以移动主数据文件。单击更新以保存更改。在“指定启动参数”框中，将参数更改为master数据库的新路径。在“现有参数”框中，选择–l参数以移动主日志文件。单击更新以保存更改。在“指定启动参数”框中，将参数更改为主数据库的新路径。

数据文件的参数值必须跟随-d参数，日志文件的值必须跟随-d参数-l参数。下面的示例显示了主数据文件的默认位置的参数值。

-dC:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\master.mdf
-lC:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\mastlog.ldf

如果计划的主数据文件重定位为E：\ SQLData，则参数值的更改如下：

-dE:\projects\DataBaseFiles\MSSQL\DATA\master.mdf
-lE:\projects\DataBaseFiles\MSSQL\DATA\mastlog.ldf

通过右键单击实例名称并选择“停止”来停止SQL Server实例。重新启动SQL Server实例。



以sa用户身份在SSMS中登录，并通过执行以下查询来验证数据库文件的位置：

USE master; SELECT * FROM sys.master_files;


全部完成。

#4 楼

您需要逐步进行操作：


关闭所有连接
ALTER DATABASE MyDB SET SINGLE_USER ROLLBACK IMMEDIATE
设置数据库状态为离线
ALTER DATABASE MyDB SET离线
到新路径
ALTER DATABASE MyDB修改文件
（
名称= MyDB，
文件名='N：\ DATA \ MyDB.MDF'
）
将数据库设置为在线状态
ALTER DATABASE MyDB SET ONLINE
设置多用户
ALTER DATABASE MyDB SET MULTI_USER

#5 楼

有一种方法可以在不使数据库脱机的情况下移动数据库数据文件（尚不确定是否可以对日志文件执行此操作）。此处：https://www.itprotoday.com/sql-server/move-database-files-without-taking-database-offline

简短版本是在新位置添加另一个数据库文件位置，然后使用带有选项EMPTYFILE的DBCC Shrinkfile将数据从旧文件移动到新文件。完成后，您可以删除旧数据文件。

不是我的解决方案，我自己在寻找该解决方案，发现它对我们的生产环境非常有用。

Thorfinn

#6 楼

请按照以下4个简单步骤操作：


打开SSMS并从窗口顶部选择New Query选项。然后复制并执行以下查询以查找要移动到新路径的数据库的查找路径，并注意您在CurrentLocation列中显示的路径。


SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID('DATABASE_NAME');



现在转到该路径并记下Database_Name.mdf和Database_Name_log.ldf文件的名称。然后执行以下两个查询以将数据库移动到特定位置。


ALTER DATABASE DATABASE_NAME MODIFY FILE ( NAME = DATABASE_FILE_NAME , FILENAME = 'NEW_PATH\DATABASE_NAME.mdf');

ALTER DATABASE DATABASE_NAME MODIFY FILE ( NAME = DATABASE_FILE_NAME_log , FILENAME = 'NEW_PATH\DATABASE_FILE_NAME_log.ldf');





现在，右键单击停止服务器在Server_Name上（您可以在对象资源管理器（左侧）中看到）。



然后将两个文件从“旧路径”移动到“新路径”，然后通过右键单击再次启动服务器在Server_Name上。通过再次执行第一步查询来确认数据库的新路径。

#7 楼

要补充现有的答案：这是一个为所有数据库创建ALTER DATABASE ... MOVE ...语句的脚本：

SELECT 'ALTER DATABASE ' + QUOTENAME(d.name) + 
       ' MODIFY FILE (name=' + QUOTENAME(f.name, '''') +
       ', filename=' + QUOTENAME(REPLACE(f.physical_name, 'C:\', 'D:\'), '''') +
       ');'
  FROM sys.master_files AS f 
       INNER JOIN sys.databases AS d ON f.database_id = d.database_id
 WHERE d.name <> 'master';

注意： REPLACE(f.physical_name, 'C:\', 'D:\')可以通过任何对文件路径进行的转换来实现。
master是免税的，因为其路径由SQL Server的启动选项确定（例如，请参阅此答案以获取详细信息）。

#8 楼

我不确定这是否是最好的方法（我欢迎任何评论告诉我它不是这样），但是它非常简单（如果您的数据库很小，也可以很快）：

首先，将数据库备份到.bak文件。
然后，从相同的.bak文件还原数据库，为还原任务选择文件选项下的新.mdf和.ldf文件位置。

我不会在维护窗口的生产环境中执行此操作，因为在还原期间无法访问数据库。我上面看到的其他方法也有类似的缺点。
还原任务完成后，您不必删除旧文件。它是自动完成的。