附加SQL Server MDF文件的不同方法

This article demonstrates different methods to attach SQL Server MDF files. First, let me explain about the database files.

本文演示了附加SQL Server MDF文件的方法。 首先，让我解释一下数据库文件。

A SQL Server database has three types of files:

SQL Server数据库具有三种类型的文件：

1. Primary Data File OR MDF File
   主数据文件或MDF文件
2. Secondary data file OR NDF File
   辅助数据文件或NDF文件
3. Log File OR LOG File
   日志文件或日志文件

主数据文件或MDF文件 (Primary data file OR MDF file)

The SQL Server database stores data in MDF files. Typically, .mdf is a preferred extension of the primary database file. It is not a type of file. You can use another extension (*.gbn) to create a primary database file without any error. The primary data file contains columns, fields, rows, indexes, tables, and data added by an application. It also contains the vital information of the database.

SQL Server数据库将数据存储在MDF文件中。 通常，.mdf是主数据库文件的首选扩展名。 它不是文件类型。 您可以使用另一个扩展名（* .gbn）创建主数据库文件，而不会出现任何错误。 主数据文件包含列，字段，行，索引，表和应用程序添加的数据。 它还包含数据库的重要信息。

辅助数据文件或NDF文件 (Secondary data file OR NDF file)

The secondary datafiles are optional. The purpose of the primary data file and secondary data file (.ndf file) are the same. Secondary data files are useful when we want to stripe the data across multiple drives of the database server. For example, if you want to keep the tables on X drive and indexes on Y drive, then you can keep the tables on the primary data file and indexes on the secondary data file.

辅助数据文件是可选的。 主要数据文件和辅助数据文件（.ndf文件）的用途是相同的。 当我们想在数据库服务器的多个驱动器之间分条数据时，辅助数据文件很有用。 例如，如果要将表保留在X驱动器上并将索引保​​留在Y驱动器上，则可以将表保留在主数据文件上，将索引保留在辅助数据文件上。

日志文件或LDF文件 (Log file OR LDF file)

It stores the changes made in the database by insert, update, delete, etc. LDF file has all the information that can be used to recover a database.

它通过插入，更新，删除等将所做的更改存储在数据库中。LDF文件具有可用于恢复数据库的所有信息。

如何找到MDF文件的位置 (How to find the location of the MDF files)

We can obtain the location of the database files by querying sys.database_files and sys.master_files dynamic management views. The difference is between sys.master_files provides the physical location of all the databases and sys.database_files provides the information of the specific database.

我们可以通过查询sys.database_files和sys.master_files动态管理视图来获取数据库文件的位置。 sys.master_files提供所有数据库的物理位置，而sys.database_files提供特定数据库的信息之间的区别。

For the demonstration, I have restored the “WideWorldImportors” demo database. You can download it from here. Now to obtain the location of database files from sys.master_files DMV, execute the following query.

为了演示，我恢复了“ WideWorldImportors ”演示数据库。 你可以在这里下载。 现在，要从sys.master_files DMV获取数据库文件的位置，请执行以下查询。

use master
go
select db_name(database_id) as [Database Name],type_desc as [File Type], physical_name as [Database File Location] from sys.master_files
where database_id>=5 --exclude system databases

Following is the screenshot of the output:

以下是输出的屏幕截图：

To obtain the information of the database files from sys.database_files DMV, execute following query:

要从sys.database_files DMV获取数据库文件的信息，请执行以下查询：

use WideWorldImporters
go
select db_name()as [Database Name],type_desc as [File Type], physical_name as [Database File Location] from sys.database_files

To view the database files location using SQL Server management studio, open SSMS Connect the database engine Expand databases right-click on “WideWorldImportors” Select properties. See the following image:

要使用SQL Server Management Studio查看数据库文件的位置，请打开SSMS。连接数据库引擎。展开数据库，右键单击“ WideWorldImportors ”，选择属性。 见下图：

In properties, select files. In “Path” and “File Name” columns show the location of the database files. See the following image:

在属性中，选择文件。 在“路径”和“文件名”列中，显示数据库文件的位置。 见下图：

We can attach the SQL Server database files using the following methods:

我们可以使用以下方法附加SQL Server数据库文件：

1. Using the SQL Server Management Studio
   使用SQL Server Management Studio
2. Using T-SQL Script
   使用T-SQL脚本

使用SSMS附加MDF文件 (Attach MDF File using SSMS)

To attach a database using SSMS, first, open SSMS connect to the database engine Right-click on “databases” select “Attach.” See the following image:

要使用SSMS附加数据库，首先，打开SSMS，连接到数据库引擎。右键单击“数据库”，选择“附加”。 见下图：

On the Attach Database dialog box, click on Add (Screen 1). On locate database dialog box (Screen 2), locate the database MDF files which you want to use to create the database. By default, the “locate database files” dialog box uses the default database file location, but you can navigate to the other location of the database file or provide the location in the “database data file location” textbox. Select the desired database file and click OK. See the following image:

在“附加数据库”对话框上，单击“添加”（屏幕1）。 在“查找数据库”对话框（屏幕2）上，找到要用于创建数据库的数据库MDF文件。 默认情况下，“ 查找数据库文件 ”对话框使用默认的数据库文件位置，但是您可以导航到数据库文件的其他位置，或在“ 数据库数据文件位置 ”文本框中提供该位置。 选择所需的数据库文件，然后单击确定。 见下图：

On attach database file dialog box, you can review the database details in “Database to attach” and database file details in the “AdventureWorks2017 database details” box. See the following image:

在附加数据库文件对话框中，您可以在“ 要附加的数据库”中查看数据库详细信息，并在“ AdventureWorks2017数据库详细信息 ”框中查看数据库文件详细信息 。 见下图：

Click on OK to attach the database. Once the database attaches successfully, you can view the database in object explorer. See the following image:

单击确定以附加数据库。 数据库成功附加后，您可以在对象资源管理器中查看数据库。 见下图：

使用T-SQL查询附加MDF文件 (Attach MDF File using T-SQL Query)

We can also attach the database using “CREATE DATABASE.. WITH ATTACH” or “exec sp_attach_db” T-SQL commands. The syntax of “CREATE DATABASE.. WITH ATTACH” command is as follows:

我们还可以使用“ CREATE DATABASE .. WITH ATTACH ”或“ exec sp_attach_db ” T-SQL命令附加数据库。 “ CREATE DATABASE .. WITH ATTACH ”命令的语法如下：

USE [master]
GO
create database <DatabaseName> ON
(name='LogicalName of the Data file', FileName='Data File Name'),
(name='LogicalName of the Log file', FileName='Log File Name')
FOR ATTACH;

For example, if you want to attach the AdventureWorks2017 database, you must execute the following command.

例如，如果要附加AdventureWorks2017数据库，则必须执行以下命令。

USE [master]
GO
CREATE DATABASE [AdventureWorks2017] ON 
( FILENAME = N'C:\MSSQL\SQLData\AdventureWorks2017.mdf' ),
( FILENAME = N'C:\MSSQL\SQLLog\AdventureWorks2017_log.ldf' )
 FOR ATTACH
GO

The syntax of the “exec sp_attach_db” command is as following:

“ exec sp_attach_db”命令的语法如下：

USE [master]
GO
EXEC sp_attach_db @dbname = N'DatabaseName',   
    @filename1 =   '<Location of the database file>',   
    @filename2 =   '<Location of the Log file>';

We can attach the adventureworks2017 database by executing following query:

我们可以通过执行以下查询来附加Adventureworks2017数据库：

USE [master]
GO
EXEC sp_attach_db @dbname = N'AdventureWorks2017',   
    @filename1 =   'C:\MSSQL\SQLData\AdventureWorks2017.mdf',   
    @filename2 =   'C:\MSSQL\SQLLog\AdventureWorks2017_log.ldf';

附加MDF文件时解决错误 (Troubleshooting errors while attaching the MDF files)

While attaching the database, you might encounter any of the following errors:

附加数据库时，您可能会遇到以下任何错误：

Access denied due to lack of permission

由于缺少权限而拒绝了访问

You might face an error “unable to open physical database file <File Name> Operating system error 5: Access denied” This error occurs because of the lack of the permissions on the database file or log files. This can be fixed by any of the following methods:

您可能会遇到错误“ 无法打开物理数据库文件<文件名>操作系统错误5：访问被拒绝 ”，因为缺少数据库文件或日志文件的权限，因此会发生此错误。 可以通过以下任何一种方法解决此问题：

1. Run SQL Server management studio as an administrator and attach the database
   以管理员身份运行SQL Server Management Studio并附加数据库
2. database files Select the 数据库文件，选择“ security tab select the appropriate user and grant 安全性”选项卡，选择适当的用户，然后将full control to the user 完全控制权授予该用户
3. If none of the above solutions work, copy the database files to the default database file locations. When we copy these files to the default database file location, the user will get the required permissions automatically
   如果以上解决方案均无效，请将数据库文件复制到默认数据库文件位置。 当我们将这些文件复制到默认数据库文件位置时，用户将自动获得所需的权限

Unable to downgrade:

无法降级：

While attaching the database, if you see “The database cannot be opened because it is version XXX” error, then make sure that you are not attaching the database files of the higher version to the lower version. For example, if you are trying to attach the database of SQL server 2014 to SQL Server 2008, you will see the following error:

附加数据库时，如果看到“ 由于版本为XXX无法打开数据库 ”错误，请确保没有将较高版本的数据库文件附加到较低版本。 例如，如果您尝试将SQL Server 2014的数据库附加到SQL Server 2008，则会看到以下错误：

The downgrade was never supported by Microsoft, so you have no options to rectify this issue.

Microsoft从未提供过降级的支持，因此您没有解决此问题的选择。

摘要 (Summary)

In this article, I have explained different types of database files (MDF, NDF and LOG files) and different ways to attach the database to SQL Server instance via SSMS and T-SQL. Also, we run through the basic troubleshooting steps to fix the errors which have been occurred while attaching the database.

在本文中，我已经解释了 不同类型的数据库文件（MDF，NDF和LOG文件），以及通过SSMS和T-SQL将数据库附加到SQL Server实例的不同方法。 此外，我们还将执行基本的故障排除步骤，以修复附加数据库时发生的错误。

翻译自: https://www.sqlshack.com/different-methods-to-attach-sql-server-mdf-files/