无法将"AdventureWorks2008“示例数据库附加到SQL Server2008中的命名实例

Question

首先附加的"Northwind“和"AdventureWorksDW2008”数据库没有问题，但是"AdventureWorks2008“失败了，错误如下。

//Msg 5120, Level 16, State 105, Line 1
 Unable to open the physical file 
"C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents".
 Operating system error 2: "2(The system cannot find the file specified.)".
 Msg 5105, Level 16, State 14, Line 1
 A file activation error occurred. The physical file name 
 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents' 
 may be incorrect. Diagnose and correct additional errors, and retry the operation.
 Msg 1813, Level 16, State 2, Line 1
 Could not open new database 'AdventureWorks2008'. CREATE DATABASE is aborted.

我在安装过程中没有使用默认的数据库实例"MSSQLSERVER“。

那么它在哪里找到路径"C:...\MSSQL10.MSSQLSERVER...\Documents“呢？

Answer 1

转到sql server配置管理器sql server服务SQL Server(MSSQLSERVER)将“登录身份”更改为“本地系统”

Answer 2

首先，您需要启用FILESTREAM用法：

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

之后，您可以创建DB：

USE [master]
GO
CREATE DATABASE [AdventureWorks2008] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLTRAININGKIT\MSSQL\DATA\AdventureWorks2008_Data.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLTRAININGKIT\MSSQL\DATA\AdventureWorks2008_Log.ldf' ),
FILEGROUP [PRIMARY] CONTAINS FILESTREAM DEFAULT 
( NAME = N'Documents', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLTRAININGKIT\MSSQL\DATA\Documents' )
FOR ATTACH
GO

更改您的实例名的TRAININGKIT实例名，然后使用sysadmin权限运行并检查路径。

Answer 3

这是我对SQL2008R2的解决方案：

1. 在Data folder下创建文件夹文档
2. Start MSSQL Mgmt Studio (以管理员身份运行)
3. 将文件流访问级别设置为已启用完全访问权限(Srv properties/Advanced)
4. Attach db AdventureWorks2008R2.mdf (连接前添加数据库和删除日志文件)