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

Question

First of all "Northwind" and "AdventureWorksDW2008" databases attached without problem, but "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"?

score 2 · Answer 1 · answered Jun 15 '11 at 12:41

2

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

answered Jun 15 '11 at 12:41

Caroline

21
2

score 1 · Answer 2 · edited Oct 11 '12 at 04:23

1

This is my solution for SQL2008R2 :

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

edited Oct 11 '12 at 04:23

Stephan

41,764
65
238
329

answered Jul 14 '12 at 11:25

NilsZen

11
1

score 1 · Answer 3 · answered Oct 29 '12 at 14:07

1

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!

answered Oct 29 '12 at 14:07

georgerazvan

11
1

Doesn't really seem like an answer... seems more like, "This answer worked for me". – Andrew Barber Oct 29 '12 at 16:23

score 1 · Answer 4 · edited Mar 05 '13 at 08:04

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:

Grant SQL service account FULL permissions to the MDF & LDF files and the Documents folder BEFORE copying them into DATA.
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).
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

score 1 · Answer 5 · answered Apr 01 '13 at 07:13

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")

Szilard Barnak · Answer 6 · 2012-03-07T20:47:55.783

1

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.

edited Mar 07 '12 at 20:47

answered Mar 07 '12 at 20:40

Szilard Barnak

46
5

In the same boat, this didn't work. I'm trying to attach a filestream enabled database to an instance of SQL that is already hosting databases that use file streams. – Levitikon Mar 13 '12 at 14:16
I found that the following may help: remove the placeholder.txt file from the Documents folder and then enable FILESTREAM and create the DB with Szilard Barnaks commands. – Josien Jul 01 '12 at 13:55

score 0 · Answer 7 · edited Oct 26 '12 at 15:31

This worked for me:

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

Place the database in the Program files(X86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA folder (or similar location for your operating system)
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.
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.
Database attached and is working perfectly without any changes to accounts or logins or any other steps.

score 0 · Answer 8 · edited Oct 06 '12 at 10:40

0

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!

edited Oct 06 '12 at 10:40

Yaroslav

6,476
10
48
89

answered Aug 02 '12 at 04:30

Jay

1

score 0 · Answer 9 · answered Jan 16 '14 at 01:58

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.

score 0 · Answer 10 · answered Jun 24 '10 at 04:01

0

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

answered Jun 24 '10 at 04:01

gordo

1

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

10 Answers10

Linked