SQL SERVER – Install Samples Database Adventure Works for SQL Server 2012

AdventureWorks is a Sample Database shipped with SQL Server and it can be downloaded from CodePlex site. AdventureWorks has replaced Northwind and Pubs from the sample database in SQL Server 2005.The Microsoft team keeps updating the sample database as they release new versions.

For SQL Server 2012 RTM Samples AdventureWorks Database is released:

You can download either of the datafile and create database using the same. Here is the script which demonstrates how to create sample database in SQL Server 2012.

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'D:\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG ;

Please specify your filepath in the filename variable. Here is the link for additional downloads.

Watch a quick video on the same subject.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

84 thoughts on “SQL SERVER – Install Samples Database Adventure Works for SQL Server 2012

  1. Hi Pinal,

    While trying to attach the file..getting some error…!
    I am using CTP3.

    CREATE DATABASE AdventureWorks2012
    ON (FILENAME = ‘D:\AdventureWorks2012_Data.mdf’)
    FOR ATTACH_REBUILD_LOG ;

    Can you please suggest how to fix this issue?

    Like

  2. i’m having difficulty with the path, it’s telling me access denied

    Msg 5133, Level 16, State 1, Line 1
    Directory lookup for the file “C:\Users\Deejay\Downloads\AdventureWorks2012_Data.mdf” failed with the operating system error 5(Access is denied.).

    Like

    • As a few other users have said…. (1) copy the .mdf data file to C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA ……. note that I am running the Express edition on my PC
      (2) You have to run sql management studio as Administrator. Right click SSMS and select ‘Run as Administrator’
      (3) Open a new query and Use Pinal’s script specifying the “FileName” as your full location path…..in step 1 above
      (4) You will get a message as the following but the database will have been created at this point so you don’t need to worry…..File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf” may be incorrect.
      New log file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\AdventureWorks2012_Data_log.ldf’ was created.
      Converting database ‘AdventureWorks2012′ from version 705 to the current version 706.
      Database ‘AdventureWorks2012′ running the upgrade step from version 705 to version 706.

      I hope this helps you.

      Like

  3. Pingback: SQL SERVER – Installing AdventureWorks Sample Database – SQL in Sixty Seconds #010 – Video « SQL Server Journey with SQL Authority

  4. The script worked for me. Although I got the following message:

    Converting database ‘AdventureWorks2012′ from version 705 to the current version 706.
    Database ‘AdventureWorks2012′ running the upgrade step from version 705 to version 706.

    Like

  5. Msg 1813, Level 16, State 2, Line 1
    Could not open new database ‘AdventureWorks2012′. CREATE DATABASE is aborted.
    Msg 948, Level 20, State 1, Line 1
    The database ‘AdventureWorks2012′ cannot be opened because it is version 705. This server supports version 655 and earlier. A downgrade path is not supported.

    — i have SQL server 2012 express. I get teh same error when i try to attach it from the UI.

    Like

  6. I Get the same Could not open new database ‘AdventureWorks2012′. CREATE DATABASE is aborted.
    Msg 948, Level 20, State 1, Line 1
    The database ‘AdventureWorks2012′ cannot be opened because it is version 705. This server supports version 663 and earlier. A downgrade path is not supported.

    Like

  7. i’m having difficulty with the Access, it’s telling me access denied
    Run sql management studio as Administrator. But no use.
    Msg 5120, Level 16, State 101, Line 4
    Unable to open the physical file “C:\DB\AdventureWorks2012_Data.mdf”. Operating system error 5: “5(Access is denied.)”.

    Like

  8. I worked for me, just need to copy the data file to the data path (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA) and run the Management Studio as Administrator.

    Like

  9. Anybody face this error ?
    —————
    Could not continue scan with NOLOCK due to data movement.
    Could not open new database ‘AdventureWorks2012′. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 601)
    ————-
    (Already build ldf)

    Like

  10. The AdventureWorks database simply does not work! Using full admin permissions doesn’t work either. It was simply shipped “f’d” up!

    Like

  11. Msg 1813, Level 16, State 2, Line 1
    Could not open new database ‘AdventureWorks2012′. CREATE DATABASE is aborted.
    Msg 948, Level 20, State 1, Line 1
    The database ‘AdventureWorks2012′ cannot be opened because it is version 705. This server supports version 655 and earlier. A downgrade path is not supported.

    Like

    • I faced the same issue but i had not installed 2012 RTM.

      –>First make sure that you have installed sql server 2012 RTM.
      –>Then, Like peizhen said just copy the AdventureWorks2012_Data.mdf to (C:\ProgramFiles\MicrosoftSQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA) and run the Management Studio as Administrator.

      Hope this helps…

      Like

  12. I am using MS SQL Server 2012 Express and this work. Just make sure you put the right path, the file in the right folder and in the right instance if you have several instances. In addition, run the database as Administrator.

    Like

  13. Pingback: Spreadsheets Made Easy - Practical information and tutorials on spreadsheets.

  14. Anyone tried installing SQL Server 2008 or 2012 Enterprise trial onto Windows 7 Home Premium? I see no documentation that it is supported or not supported, but having many problems.

    Like

  15. guys, actually I’m having trouble attaching the 2012 databases using the above create/attach script. Anyone tried on a Windows 7 Home Premium OS?

    Like

  16. I made sure to install every available feature of SQL Server 2012, downloaded the AdventureWorks 2012 database to C: , opened SQL Server Studio as Administrator, and ran create/attach script. I keep getting File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf” may be incorrect.
    Msg 5123, Level 16, State 1, Line 1
    CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\AdventureWorks2012_log.ldf’.
    Msg 1813, Level 16, State 2, Line 1
    Could not open new database ‘AdventureWorks2012′. CREATE DATABASE is aborted.
    Based on Pinal’s example, the .mdf file doesn’t need to be in any specific folder, as long as I use the correct path in the create/attach script.
    Please, any advice?

    Like

  17. I also have a copy of this .mdf file in my Downloads folder. If I run this:
    CREATE DATABASE AdventureWorks2012
    ON (FILENAME = ‘C:\Users\Aitch\Downloads\AdventureWorks2012_Data.mdf’)
    FOR ATTACH_REBUILD_LOG ;

    I get the following error:
    Msg 5133, Level 16, State 1, Line 1
    Directory lookup for the file “C:\Users\Aitch\Downloads\AdventureWorks2012_Data.mdf” failed with the operating system error 5(Access is denied.).

    Like

  18. I keep gettin this error …. Tried all sorts of things, none worked.

    Msg 1813, Level 16, State 2, Line 1
    Could not open new database ‘AdventureWorks2012′. CREATE DATABASE is aborted.
    Msg 948, Level 20, State 1, Line 1
    The database ‘AdventureWorks2012′ cannot be opened because it is version 706. This server supports version 655 and earlier. A downgrade path is not supported.

    Like

  19. Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file “c:\AdventureWorks2012_Data.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.

    I have this Error the file in c

    Like

    • I am also getting this error[Msg 5120, Level 16, State 101, Line 1
      Unable to open the physical file “c:\AdventureWorks2012_Data.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.], can somebody help us out here??

      1. made sure admin and logon user has full control on directory, Tried from custom folder and also from C:\ SQL path
      2. Try to attach through IDE, same error..

      thanks in advance,
      HydTechie

      Like

  20. this isn’t working…help

    while (dataReader.Read())
    {
    int departmentId = dataReader.GetInt32(0);
    string Name = dataReader.GetString(1);
    string GroupName = dataReader.GetString(2);
    Console.WriteLine(
    “DepartmentID: {0}\nID: {1}\n{2}\n”, Name, GroupName);
    }

    Like

  21. Msg 1813, Level 16, State 2, Line 2
    Could not open new database ‘MyAdventureWorks’. CREATE DATABASE is aborted.
    Msg 948, Level 20, State 1, Line 2
    The database ‘MyAdventureWorks’ cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.

    Like

  22. Pingback: SQL SERVER – Restoring 2012 Database to 2008 or 2005 Version and 2 other Most Asked Questions « SQL Server Journey with SQL Authority

  23. Now you have help me again,again and again on so many questions about SQL-Server. Now and here a gread THANKS!!!!!! for your work. J.M.-S.

    Like

  24. I copied file path of exact location of .mdf folder, Operating System error 2?
    Can you please explain.

    Thanks
    Chalrotte

    Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\SQLDATA\AdventureWorks2012_Data.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.

    Like

  25. Hi Pinal Dave,
    You are rocking… Ur blogs help me to resolve so many work place issues. Great. I have a query regarding Adventure works project. Does it comes with UI ? I mean front end as well ? I need a sample application which uses MS SQL to understand. Any pointers to this is really appreciated.

    Thanks again for great blogs :)

    Like

  26. I’m getting an incorrect syntax error on ‘FOR ATTACH_REBUILD_LOG';
    It states it’s expecting an ‘ID’. What ID ?

    Help! I’m a REAL novice at this.

    Like

  27. Pingback: SQL SERVER – Weekly Series – Memory Lane – #020 | SQL Server Journey with SQL Authority

  28. Thank you very much for this extremely helpful post! I encountered only 2 issues which I quickly figured out. The first was the script failed the first time I ran it because it said I did not have permissions; so I had to go to the data file I downloaded, right click on it, go to Properties>Permissions and give myself Full Control. I then re-ran script and got the desired results that matched what you show. Second, maybe just because I was tired, I didn’t see the AdventureWorks database automatically appear in my SQL Management Studio window as you show – then realized I had not connected to it. Once I did, and selected the database, all was good. These might be trivial for experienced SQL users, but wanted to pass along for any other amateurs like me :) Thanks again – great job and VERY helpful!!!

    Like

  29. i down loaded the adventureworks 2012 for sql 2012 FROM your link but humanresources.employee table is not giving me the the table with employeeid, managerid and so on. instead it gives me businessEntityID, MARITAL STATUS, GENDER, BIRTHDATE, HIREDATE etc etc… i don’t know if i downloaded something wrong.

    Like

  30. I have created a cube in SSSAS. And Now I want to create reporrt using that cube.
    In report I have to create cascading parameters (Example: I have two parameter country and city.
    So If I select country then their respective Citys should come in City parameter )

    So any one of you could you please let me know that How Can I create cacscading parameter?

    Should I create some hierarchy in SSAS?
    Or I have to do something at report level ??

    Like

  31. Pinal, your instructions are of less value by your not dealing with the common permission and connection issues that people face when downloading and trying to install. Further, your narrative presentation leaves much to be desired, at least for English-speaking citizens of the U.S. . I understand your ‘English’ ; but bad diction, pronunciation, and obfuscating accent are not helpful. You perhaps would do somewhat better if you limited yourself to writing only the script for narration; but as someone who speaks, reads; writes three languages(including English); and knowing some vocabulary in Hindi, Urdu, and Punjabi; I would not attempt to give a presentation in Hindi of even something as simple as this is technically; that was full of mispronunciations, mixing of tenses, bad sentence construction, and misspellings…Other than that; I appreciate your efforts…

    Like

      • Hi Pinal,
        Thank You! The notice of the upgrade of Adventure Works is welcome news to those developers among us who are looking to create great templates for MVC4, EF5, HTML5, CSS3 sites with services and I continue to be a grateful follower of your insights into the SQL Server world!

        Like

  32. I still have this error:

    Msg 1813, Level 16, State 2, Line 1
    Could not open new database ‘AdventureWorks2012′. CREATE DATABASE is aborted.
    Msg 948, Level 20, State 1, Line 1
    The database ‘AdventureWorks2012′ cannot be opened because it is version 706. This server supports version 662 and earlier. A downgrade path is not supported.

    Like

  33. hi pinal , i am getting this error can u give me reply for this error I would be grateful to you
    Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file “E:\AdventureWorksDW2012_Data”. Operating system error 2: “2(The system cannot find the file specified.)”.

    Like

  34. Msg 5133, Level 16, State 1, Line 1
    Directory lookup for the file “D:\AdventureWorks2012_Data.mdf” failed with the operating system error 21(The device is not ready.).

    I get this error

    Like

  35. Hey there just wanted to give you a quick heads up.
    The words in your post seem to be running off the screen in Firefox.
    I’m not sure if this is a formatting issue or something to do with internet browser compatibility but I thought I’d post
    to let you know. The design look great though! Hope you get the problem resolved soon.

    Kudos

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s