Unable to attach "AdventureWorks2008" Sample Database to a named Instance in SQL Server 2008

First of all "Northwind" and "AdventureWorksDW2008" databases attached without problem, bu开发者_如何学Pythont "AdventureWorks2008" fails with the following error.

//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.

I did not use the default database instance "MSSQLSERVER" during install.

So where is it finding this path "C:...\MSSQL10.MSSQLSERVER...\Documents"?

go to sql server configuration manager sql server services SQL Server(MSSQLSERVER) change the "log on as" to "local system"

First of all you need to enable the FILESTREAM usage:

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

After that you can create the 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

Change the TRAININGKIT instance name for your instance name and check the paths after that run with sysadmin privileges.

This is my solution for SQL2008R2 :

1. Create folder Documents under Data folder
2. Start MSSQL Mgmt Studio (run as Admin)
3. Set Filestream Access Level to Full Access Enabled (Srv properties/Advanced)
4. Attach db AdventureWorks2008R2.mdf (add db and remove Log file before attaching)

just like SQL Guy! i enabled FILESTREAM from configuration manager=>services=>yourinstance=>right-click=>properties=>advanced and then i you can remove the necessity of the log file from the attachement menu ( right click on database in sql => attach...). and it worked!

It was appearing to work, but becoming Suspect after SQL service restart. SQL Log said:

Message FILESTREAM's file system log record 'placeholder.txt' under log folder '\?\C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents\$FSLOG' is corrupted.

Combining various suggestions found above, I got it to work (and survive service-restart without going Suspect) by:

1. Grant SQL service account FULL permissions to the MDF & LDF files and the Documents folder BEFORE copying them into DATA.

2. Delete C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents\$FSLOG\placeholder.txt (R2 BOL hints at this, but directs it at the wrong level).

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

Solved it by below ways:

I fixed it As the .mdf and .ldf files where on different drives, it was giving some wierd error so i attached only mdf files and the server created .ldf files on the same drive as mdf. this way i could attach it but then i took the db offline copied the log from D:\ to E:\ and then brought the db online. it worked.

and sometimes you can :

try login with Windows Authentication and
Start SQL Server manager as Administrator (right-click on the program, choose "Start as administrator")

Seems like many are having this issues, including me, and including the guy who logged this bug with MS Connect:

http://connect.microsoft.com/SQLServer/feedback/details/567193/adventureworks-2008-fails-to-attach

This is the only way I could get it to work. (Install without filestream) Go to Download 'AdventureWorks 2008R2 OLTP Script'

Unzip and open the instawdb.sql script. Remember to set the variables at the top of the script. Run the script. This should bulk load all the data as well. Good luck!

This worked for me:

Software used-SQL Server 2008 Express R2, Windows Vista OS, Adventureworks2008R2.mdf

1. Place the database in the Program files(X86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA folder (or similar location for your operating system)
2. All my installation files are in the default. This was done on Windows Vista with SQL SERVER Express 2008R2. The database used was Adventureworks2008R2.mdf.
3. IMPORTANT PART---After picking the database in SSMS ATTACH DIALOGUE BOX you must Remove the log file first (within the Attach dialogue box), (.ldf file). If you do not Remove this file the attachment will fail with an error message.
4. Database attached and is working perfectly without any changes to accounts or logins or any other steps.

In my case the problem had to do with the file I was using and the version of SQL. I have installed SQLServer2008R2 and the file I was using was AdventureWorks2008_Data.mdf. I downloaded the one from here http://msftdbprodsamples.codeplex.com/releases/view/59211 (AdventureWorks2008R2_Data.mdf) and all is fine now.