We help IT Professionals succeed at work.

Finding/installing Adventureworks2008R2 DB

RichNH asked
Medium Priority
Last Modified: 2012-08-13
Hi all, not two weeks ago I found and installed the sample DBs from MS for SQL Server 2008 R2.  This installed several DBs, various versions of Adventureworks DB.  I was using Adevtureworks2008R2 which matched all the examples in the text I am using.  

Somehow I corrupted it (it didn't have any objects in it, no tables, no nothing).  I figured I would just install a new copy so I deleted it.  Not only did I delete it but I deleted all the Adventureworks sample DBs that were loaded by the program I ran.  

Then I went back to http://msftdbprodsamples.codeplex.com/ which is where I got the original install and ran the load program for SQL Server2008 R2 which is what I ran last time, it installed a bunch of DBs but the one DB I was using, the Adventureworks2008R2 DB is missing!  What's up with that?  Did MS change the program to not load this DB?  All the other DBs are there except for the one DB I was using.

Any ideas?
Watch Question


BTW, Am I wrong in assuming that when I download these executables that the entire DB load in contained in the executable?  If I eventually find the executable that will also load the Adeventureworks2008R2 DB, can I download that executable to my PC and whenever I want to reinstall the DBs can I simply run this executable even if the PC isn't hooked up to the internet?   Or do I need to be online when I run it because the executable pulls additional data from the internet?
Marten RuneSQL Expert/Infrastructure Architect
Your second question is answered in MS's text on the site.
Quote:"... All of the AdventureWorks sample databases for SQL Server 2008R2 are included in the self extracting zip file..."

I e you dont need internet connection when installing.

Your main issue, could be a bunch of things. Look in eventviewer for windows and the SQL Server errorlog for clues to whats going on.

Regards Marten
First lessons learned.
- Backup
take a (sql) backup before you start changing things, if you're not familiar with SQL Server yet stop sql server engine and then a normal filebackup will do
- Test
Actions that delete all your objects and then all your databases.... what are you gambling with?  

Only adventureworks db-file you can find on this link where also the instructions to attacht it http://msftdbprodsamples.codeplex.com/releases/view/59211


OK, that's good news, so if I find a copy of the exe that includes the DB I'm looking for I will save the exe to my PC and have it for the future if this occurs again.  

Why would MS fail to include the Adventureworks2008R2 DB in it's exe file?  It was there two weeks ago!  Now it's not?  This is very strange.  I will not trust MS anymore in matters like these, I should have saved the exe I used two weeks ago.  Do you suppose this was an oversight by them which they will fix or do you think this omission was deliberate?

<<OK, that's good news, so if I find a copy of the exe that includes the DB I'm looking for I will save the exe to my PC and have it for the future if this occurs again.  >>
Backup your database, the install you can download again. Changes in your database as procedures you build .... get lost for ever.  And yes it's a trial database , but  taking a sql backup is also part of learning to work with databases.  In fact it's the most crucial thing you must know about databases, your business or job depends on not loosing your databases.


<<Why would MS fail to include the Adventureworks2008R2 DB in it's exe file? >>
Who say's it isn't. I think it still is there but it for example fails because of it isn't a clean install anymore (for example the file still exists)  and maybe in eventviewer there is a message telling what went wrong.
In comment of 2008R2 SR1 I read
"Some users have been unable to successfully install the AdventureWorks2008R2 OLTP database using the SR1 installer.  We have added a version of the database without FILESTREAM as a single file that you can download and attach. To install this version, go to AdventureWorks2008R2 without filestream.

Please give us your feedback in the samples forum! (Yes, even the complaints. We do try to respond to them.)"

Before you loose trust in 'general sollutions' maybe you must ask yourself , is it possible that while messing things up you messed it up in such way the 'general sollution' may not  fit your case anymore.


Jogos, Thanks for your comments, as you can tell, when it comes to SQL Server I'm a babe in the woods, even at this old age.    I found the mdf file you spoke of and started to look at it and try to understand what was going on.  This is what I have learned and discovered.

I have two "program files" folders in WinExplorer, \Program Files and \Program Files (x86).  My SQL Server Mgt Studio icon on my desktop points to the \Program Files (x86) folder for the executable (where it looks like a complete set of SQL Server is installed), however it appears all the .mdf files for Adventureworks are located in another (incomplete)installation of SQL Server which is in the /Program Files area.  (the \Program Files area doesn't appear to contain a complete set of exe files as there wasn't a copy of the Mgt Studio executable in that \Program Files folder but there was in the (x86) folders.)

So I found all the .mdf & ldf Adventureworks files on my C: drive and the dates on the files are accurate for my activities.  So I know where the physical data files are located.  Now, I have an external Seagate backup disk installed on my laptop and once I realized it was a matter of getting an older copy of the .mdf file I started poking around it.  I found the Adventureworks2008R2.mdf files and there were several copies going back a few days SO...

I went back into my C drive located the Adventureworks2008R2.mdf files and renamed it to a different name.  This was the DB that I had in the management studio but had "deleted" and never saw again in Mgt Studio (but I still see the original physical file).  So it was renamed.

Then I went back to the external backup drive and located a copy of the same file that was a few days old, I copied that and pasted it into the folder where all the Adventireworks files are located.   It came in with a name that included a date/time stamp of when it was backed up.  I renamed it to the official Aventureworks2008R2.mdf, now I have the mdf file there without an accompanying ldf file (I renamed the old ldf file too).

Then I went into the management studio.  I right clicked on Databases and click Attach.  It gave me the attach db dialog box and I saw the file I had placed in the folder from the backup drive.   I also saw the file I had renamed.  I selected the file I had put there from the backup drive.  I clicked OK and came up with an error message.  I tried several different ways to get around the different error messages which I was seeing but decided to start over new so I completely deleted Adventureworks2008R2.* from the directory.  

I went back to the backup drive and dragged the following file to the data directory  
AdventureWorks2008R2_Data (3-8-2012 7!10!45 PM).mdf.  Now I know this file was good because I had succesfully worked with the DB on 3/9.  In fact, thinking back, I never really made many changes to the DB, just sort of entered the example queries from the book.   Anyway, it was working fine on 3/9 but then my PC crashed due to heat.  Morning of 3/10 it was toast,  The Db was open I think when the PC crashed.  ANYWAY...   As I mentioned, I deleted this DB from the Mgt Studio and am now trying to attach the backup.

I renamed the file I dragged from the backup disk and started up Mgt Studio again and tried the same operation again, attaching the file.  It loaded the file in and also identified a log file which it said was "not found".  My understanding is that if the log file isn't found, it would create a new one.  However it fails on a log file not found.  

Looking things over I saw a way to create the script that was generated in a query window.  So I did that and then looked at the query window.   It was trying to attach a log file too.   I deleted the reference to the log file and tried again.   It said this:

File activation failure. The physical file name "c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AdventureWorks2008R2_Log.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'AdventureWorks2008R2'. CREATE DATABASE is aborted.

So, this is where I stand right now.  Let me try that attach operation with the file you pointed me to, now that I've learned how to do it.


Using the MDF file that you suggested I download I tried creating the script through the attach option in the Mgt Studio.   I modified the script which referenced the log file to say this:

USE [master]
CREATE DATABASE [AdventureWorks2008R2] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AdventureWorks2008R2_Data.mdf' )
 FOR ATTACH_rebuild_log


I have to run and get ready for church.   When I get home I'll look and see if everything is cool with the attached DB.


Thanks all, It looks like things are GTG now.  I shared the points a bit for Martin's answer about the files being all inclusive.