数据库mdf和ldf文件

How often you got to the point that for any reason you don’t have enough space on the specific drive to host a database? In case of database development or other tasks outside the production environment, this should not be a problem as a database can be eventually re-created, restored from a backup and set to be hosted on another location. By default, SQL Server stores database files in its installation folder, specifically in the Data folder:

您多久一次发现由于某种原因您在特定驱动器上没有足够的空间来承载数据库？ 对于数据库开发或生产环境之外的其他任务，这应该不成问题，因为可以最终重新创建数据库，从备份中还原数据库并将其设置为托管在其他位置。 默认情况下，SQL Server将数据库文件存储在其安装文件夹中，尤其是在Data文件夹中：

However, what if a SQL database is a production one or it is being constantly used by the specific application? This means that when it comes to the point of a low disk space, there should be a way to move database files (MDF and LDF) to another drive (with sufficient disk space) while the actual database will still be hosted by the same SQL Server instance. Let’s see how to move database files to another location. We’ll use a sample AdventureWorks database in this case.

但是，如果SQL数据库是生产数据库或特定应用程序经常使用它，该怎么办？ 这意味着，当磁盘空间不足时，应该有一种方法可以将数据库文件（MDF和LDF）移动到另一个驱动器（具有足够的磁盘空间），而实际的数据库仍将由相同SQL托管。服务器实例。 让我们看看如何将数据库文件移动到另一个位置。 在这种情况下，我们将使用示例AdventureWorks数据库。

先决条件 (Pre-requisites)

In case a database is being used by any Windows services or other resources, these must be stopped in order to allow altering SQL database files. Also, any existing connections to a database must be closed. Before the first step, make sure to locate the appropriate MDF and LDF files for a database you want to work with. By default, these names are in the following format:

如果任何Windows服务或其他资源正在使用数据库，则必须停止这些服务以允许更改SQL数据库文件。 另外，必须关闭与数据库的任何现有连接。 在第一步之前，请确保为要使用的数据库找到合适的MDF和LDF文件。 默认情况下，这些名称的格式如下：

• Database_name_Data.mdf – for MDF file
  Database_name_Data.mdf –用于MDF文件
• Database_name_log.ldf – for LDF file
  Database_name_log.ldf –用于LDF文件

The above mentioned format does not need to be necessarily used, so make sure you are targeting correct files.

不必一定使用上述格式，因此请确保目标文件正确。

将数据库文件移动到另一个位置 (Moving database files to another location)

• ALTER DATABASE AdventureWorks2014       MODIFY FILE ( NAME = AdventureWorks2014_Data,                     FILENAME = 'E:\New_location\AdventureWorks2014_Data.mdf');  GO ALTER DATABASE AdventureWorks2014       MODIFY FILE ( NAME = AdventureWorks2014_Log,                     FILENAME = 'E:\New_location\AdventureWorks2014_Log.ldf');  GO

The New_location is a folder created on a separate drive (in this specific case, we will change from a default C to E drive on a local machine) with sufficient disk space for SQL database files. Specified folder must be created first, in order to be used as a new location for SQL database files in the above SQL statement

New_location是在单独的驱动器上创建的文件夹（在这种情况下，我们将从本地计算机上的默认C盘更改为E盘），并具有足够的磁盘空间来存储SQL数据库文件。 必须首先创建指定的文件夹，才能用作上述SQL语句中SQL数据库文件的新位置

• ALTER DATABASE AdventureWorks2014 SET OFFLINE;  GO

This is important in order to perform the next step. If a database is being used by any application, this step cannot be accomplished, unless all connections to a database are closed.

这对于执行下一步很重要。 如果任何应用程序正在使用数据库，则除非关闭与数据库的所有连接，否则无法完成此步骤。

• Move MDF and LDF files of the specific SQL database to a new location specified in the statement above. This means to simply cut mentioned files from the existing location and to move them to a newly specified one.
  将特定SQL数据库的MDF和LDF文件移动到上面的语句中指定的新位置。 这意味着只需从现有位置剪切提到的文件，然后将它们移动到新指定的文件即可。

Important note: Make sure that SQL Server can access the specified location. Otherwise, the following error will appears:

重要说明 ：确保SQL Server可以访问指定的位置。 否则，将出现以下错误：

Msg 5120, Level 16, State 101, Line 13

Unable to open the physical file “E:\New_location\AdventureWorks2014_Data.mdf”. Operating system error 5: “5(Access is denied.)”.

讯息5120，第16级，州立101，第13行

无法打开物理文件“ E：\ New_location \ AdventureWorks2014_Data.mdf”。 操作系统错误5：“ 5（拒绝访问。）”。

To fix this:

要解决此问题：

• Start SQL Server Configuration Manager
  启动SQL Server配置管理器
• Properties option from the drop-down list: 属性”选项：

Instead of the current account, switch to the one that has access to a drive where files are moved:

切换到有权访问文件移动驱动器的帐户，而不是当前帐户：

• ALTER DATABASE AdventureWorks2014 SET ONLINE;  GO

• SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatusFROM sys.master_files  WHERE database_id = DB_ID(N'AdventureWorks2014')  GO

This should give the following result:

这应该得到以下结果：

Once this is done, a SQL database will be hosted on a drive with sufficient free space and the user can continue using it.

完成此操作后，SQL数据库将托管在具有足够可用空间的驱动器上，并且用户可以继续使用它。

翻译自:

数据库mdf和ldf文件