SQL Server附加数据库(操作系统错误 5:“5(拒绝访问。)”)

时间:2024-11-10 07:03:50

SQL Server 2012中数据库附加的问题:

1、问题出现场景:(已知,可能包含我并不知道的因素,所以我没有办法保证按照以下条件设置后,一定会再现提示的错误)

(1)原数据库主文件(.mdf)存放在fat32分区;

(2)将要附加文件文件从fat32分区拷贝到NTFS格式的分区的指定目录中;

(3)附加文件;

2、错误提示描述:

(1)错误现象图片


(2)错误现象描述

标题: Microsoft SQL Server Management Studio
------------------------------

附加数据库 对于 服务器“JZT-PC\MYSQLSERVER2012”失败。  ()

有关帮助信息,请单击: /fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1846+)&EvtSrc=&EvtID=附加数据库+Server&LinkId=20476

------------------------------
其他信息:

执行 Transact-SQL 语句或批处理时发生了异常。 ()

------------------------------

无法打开物理文件“D:\MyData\AdventureWorks2012_Data.mdf”。操作系统错误 5:“5(拒绝访问。)”。 (Microsoft SQL Server,错误: 5120)

有关帮助信息,请单击: /fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

------------------------------
按钮:

确定
------------------------------

3、错误解决:

(1)根据给出错误提示“.mdf文件拒绝访问”,初步判断是权限问题;

(2)在附加文件目录下找到.mdf文件,点击右键,查看“属性”,如下图进行操作:


(3)重新到management studio中进行附加操作:


仍然会有错误提示给出,但是注意观察,这次提示的内容已经不再是.mdf文件,而是.ldf文件,所以对.ldf文件进行同.mdf一样的处理。(步骤同第2步)

(4)再附加,不再提示错误,最终效果如下图:


可以看到一个新的数据库已经添加,名称对应的就是我们那个主数据文件的名称。


注意:

1、以上情况在fat32的分区上附加文件,不会出现问题;在fat32分区上的数据文件,只要按照“附加”向导提示的一步一步进行,可以很顺利的附加数据库文件;


2、有关ntfs和fat32文件系统区别的相关信息的链接:

(1) /Q/What_is_the_difference_between_NTFS_and_FAT32

A computer filing system is how your computer manages the information that is stored on its hard drive. The most common Windows filing systems are the FAT16, FAT32 and the NTFS filing systems. The FAT32 is the universally standard system because can be used by more versions of Windows than the others.
FAT32 filing system is the system used on older Microsoft Windows systems including Windows 95, Windows 98 and Windows Me. The name FAT32 describes how data is stored on the hard drive, in 32 bit chunks. A FAT32 filing system can be installed,
but is not recommended, on Windows 2000, Windows XP and Windows 2003. The FAT 32 filing system is superior to the older FAT16 system in that it supports disk partitions as large as two terabytes (2,048 gigabytes), whereas FAT16 only supports two gigabytes. However, FAT 32 also has limitations. The FAT32 system does not allow disk
compression using DriveSpace, it is not compatible with older disk management software and it runs a bit slower that FAT 16.
While the Windows XP, 2000 and 2003 can support the FAT32 system, it is recommended they run an NTFS (new technology file system) because it is a can handle more functions that the FAT32, including the capacity to support Unicode file names, proper security, compression and encryption. The NTFS system is supported by the windows nt, 2000, XP and Vista and 7, and is able to handle much more memory than either the FAT16 or FAT32 systems. The NTFS also allows computers to more efficiently use the space available to store information on their hard drive, provides a better system of cataloging files on your hard drive for better and faster retrieval, and provides better methods for executing basic file operations such as delete, copy and rename.

(2)/zh-cn/library/windows/desktop/#functionality

(3)/zh-cn/library/windows/desktop/