attaching adventureworks2008 on full version of sql server

The Wrecking Bawl Destructuring query language, one keyword at a time.

<< sharepoint 2007 (moss) system account | Home | workspaces in sql mgmt studio >>

For anybody that bought the SQL Server 2008 Self-Paced Training book for 70-433, if you are running a full version of SQL Server 2008 rather than the express edition, you will have problems attaching the AdventureWorks2008 database (file activation error). The trick is to use a SQL command, like so:

USE [master]
GO
CREATE DATABASE [AdventureWorks2008] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\AdventureWorks2008_Data.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\AdventureWorks2008_Log.ldf' ),
FILEGROUP Documents CONTAINS FILESTREAM (NAME = Documents, FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Documents')
FOR ATTACH
GO Posted on Tuesday, June 9, 2009 12:39 PM all tech stuff , SQL Server | Back to top

Comments on this post: attaching adventureworks2008 on full version of sql server

# re: attaching adventureworks2008 on full version of sql server

not working, "Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\AdventureWorks2008_Data.mdf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 1815)."
What's the problem?

Left by Mt on Aug 18, 2009 12:21 AM

# re: attaching adventureworks2008 on full version of sql server

I racked my brain for 2 days. Searched numerous blogs on the net for a solution to attach AdventureWorks2008 database but to no vail. Your script did the trick. Thanks heaps for sharing.

Left by Rov on Nov 04, 2009 4:42 AM

# re: attaching adventureworks2008 on full version of sql server

I keep having the same problem as MT and:
Msg 5134, Level 16, State 2, Line 1
The path that is specified by 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\mssql\DATA\Documents' cannot be used for FILESTREAM files because it is not on a supported file system.

So yes. Fun times. Any ideas anyone? I'm running XP Pro on a fat32 if that has anything to do with it.

Left by Nelson on Nov 23, 2009 3:26 AM

# re: attaching adventureworks2008 on full version of sql server

Okay so I figured it out. My problem was that I was running windows on a Fat32 partition. I reformatted the partition as NTFS and followed all the default instructions for installing the AdventureWorks data pack 3 from codeplex. AdventureWorks2008 installed without a hitch. I'll just deal with not being able to write to that partition while booted into OSX.

Left by Nelson on Nov 26, 2009 7:03 AM

# Ms

This is wonderful. I looked everywhere for a workaround, and this solved the problem. All I had to do was replace MSSQL10.SQL2008 with MSSQL10.MYINSTANCENAME
in the file names and it attached great. Back to studying for my exam!

Left by Lisa Morgan on Feb 04, 2010 10:16 AM

# re: attaching adventureworks2008 on full version of sql server

Thank you very much..i am trying this for 1 week..your script did it

Left by Rams on Feb 15, 2010 3:40 AM

# re: attaching adventureworks2008 on full version of sql server

Thanks for this - I struggled to attach this db for 2 days before I found your post. I'd love to know why this db will not attach using the GUI driven command.

Left by Dickon on Jul 03, 2010 5:27 AM

# re: attaching adventureworks2008 on full version of sql server

Hi. Just wanted to say thank you for this post. I had searched for days trying to find a solution. This worked perfectly.

Left by Rick Mays on Dec 16, 2010 10:02 AM

# re: attaching adventureworks2008 on full version of sql server

Thanks a ton for the script. i was breaking my head over the issue in running advanceworks2008 db. finally it worked fine after running the above scripts.. thanks a lot again..

Left by paddy on Dec 23, 2010 8:18 AM

# re: attaching adventureworks2008 on full version of sql server

Just wanted to thank you! this was very helpful, i had to activate the FILESTREAM from the configuration manager, but that was it! thanx again for your sharing! ;)

Left by Rolo on Jun 18, 2011 10:03 PM

# re: attaching adventureworks2008 on full version of sql server

"...you the man!"

Left by Lee on Jun 21, 2011 7:40 AM

# re: attaching adventureworks2008 on full version of sql server

first it didn't work for me.
but i made this change:
right click on the server name->properties->Advanced->Filestream Access level->FULL ACCESS ENABLED

Left by amir on Aug 01, 2011 3:29 AM

# re: attaching adventureworks2008 on full version of sql server

like amir , for me the problem was only enabling 1-filestream through configuration manager
and then
2-EXEC sp_configure filestream_access_level, 2
RECONFIGURE

alternative for
Filestream Access level->FULL ACCESS ENABLED
from object explorer in SSMS
3- and then the normal attach command from SSMS worked

How to: Enable FILESTREAM
http://msdn.microsoft.com/en-us/library/cc645923%28v=sql.100%29.aspx

Left by iman on Aug 25, 2011 3:15 AM

# re: attaching adventureworks2008 on full version of sql server

u saved the day man

Left by njaps on Sep 05, 2011 4:07 AM

# re: attaching adventureworks2008 on full version of sql server

Great. It worked for me. I was searching the net for along. Thanks a lot

Left by Anil on Dec 04, 2011 9:57 PM

Your comment:

Title:

Name:

Email: (never displayed) (will show your gravatar)

Comment: Allowed tags: blockquote, a, strong, em, p, u, strike, super, sub, code

Verification: