7

I'd like to install AdventureWorks2008 (I just install SQL Server 2008 R2 Express).

Each time I download the recommended version from CodePlex, all I get is a AdventureWorks2008.mdf file. Not only I cannot attach the file from SQL Server Management Studio, but I cannot copy/paste the file directly into the the database.

I've read in several places that I need to use AdventureWorks2008.msi, but I cannot find where to download it.

I just cannot figure out how to install AdventureWorks2008

Thanks for helping

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Richard77
  • 20,343
  • 46
  • 150
  • 252
  • What happens when you try to attach the file in SSMS? Do you get an error? – Paul Sasik Jun 03 '12 at 13:02
  • I tried that SQL command and also manual attaching of the mdf file. Both methods cause the same error. My agony is at - http://stackoverflow.com/questions/19060441/cannot-install-adventureworks-2012-database-operating-system-error-5-access-i – Steam Sep 27 '13 at 22:19

2 Answers2

9

There isn't an .msi file for adventureworks, even though you'll find it mentioned in outdated documentation and books. You aren't alone in finding this confusing -- it seems the web site, files and steps Microsoft provides for installing these databases changes every time I need to install them.

You need to create the database and attach the .mdf file, which is the "data file" referred to in the instructions. (.mdf = primary data file, .ldf = log file, .ndf = secondary data file)

In order to attach the file, you need to make sure you carefully follow the steps here: http://social.technet.microsoft.com/wiki/contents/articles/3735.sql-server-samples-readme-en-us.aspx#Readme_for_Adventure_Works_Sample_Databases

Instructions for 2008R2:

To install AdventureWorks2008R2 OLTP database

  1. Download the AdventureWorks2008R2 Data File.

  2. From File Download, click Save and browse to a location on your local server.

  3. From SQL Server Management Studio, execute the following code:

Case-insensitive Database

CREATE DATABASE AdventureWorks2008R2 
ON (FILENAME = '{drive}:\{file path}\AdventureWorks2008R2_Data.mdf') 
FOR ATTACH_REBUILD_LOG;

As an alternative to step 3, you can attach the database using the SQL Server Management Studio user interface. For more detailed information, see Attach a Database (SQL Server Management Studio).

Note: You must remove the log file from the list of files to attach. This will cause the operation to rebuild the log.

Headache saving tip from Aaron Bertrand:

You should place the mdf file in your normal data folder - SQL Server will already have the proper permissions. You can get this path using

SELECT TOP (1) physical_name FROM master.sys.database_files;

Jon Crowell
  • 21,695
  • 14
  • 89
  • 110
  • By "AdventureWorks2008R2 Data File", you mean the one that has .mdf extension? If that's the case, I have already downloaded it. But I don't see any "Licence Agreement". The only file I get is the "AdventureWorks2008R2.mdf" – Richard77 Jun 03 '12 at 13:24
  • 1
    Yes, the data file is the .mdf. I just copied Microsoft's instructions from the site, so if there isn't a license agreement, just skip that step. – Jon Crowell Jun 03 '12 at 13:26
  • I edited my answer to remove the eula acceptance step. I also added a legend for the sql server file extensions. – Jon Crowell Jun 03 '12 at 13:32
  • Now I'm getting the following error: "Msg 5120, Level 16, State 101, Line 3 Unable to open the physical file "C:\AdventureWorks2008OLTP\AdventureWorks2008R2_Data.mdf". Operating system error 5: "5(Access is denied.)"." – Richard77 Jun 03 '12 at 14:13
  • 1
    See if either of these links help you: http://connect.microsoft.com/SQLServer/feedback/details/539703/access-denied-attaching-a-database-when-permissions-are-inherited http://stackoverflow.com/questions/2330439/access-is-denied-when-attaching-a-database – Steve Kass Jun 03 '12 at 15:00
  • 2
    @Richard77 you should place the mdf file in your normal data folder - SQL Server will already have the proper permissions. You can get this path using `SELECT TOP (1) physical_name FROM master.sys.database_files;` – Aaron Bertrand Jun 03 '12 at 15:38
  • @AaronBertrand: do you mind if I include your comment in my answer? (attributed, of course!) – Jon Crowell Jun 04 '12 at 12:52
  • @HeadofCatering not a problem. – Aaron Bertrand Jun 04 '12 at 12:53
  • I tried that SQL command and also manual attaching of the mdf file. Both methods cause the same error. My agony is at - http://stackoverflow.com/questions/19060441/cannot-install-adventureworks-2012-database-operating-system-error-5-access-i – Steam Sep 27 '13 at 22:19
  • Did you try what Steve and Aaron suggest in the comments above? That resolved the issue for Richard77. – Jon Crowell Sep 28 '13 at 20:32
4

You can directly paste that file into your database directory. For more information you can refer http://tryingmicrosoft.com/error-while-attaching-a-database-to-sql-server-2008-r2/.

shadab shah
  • 551
  • 1
  • 6
  • 8