SQL : How to solve Northwind Database Install Error in SQL Server 2012

SSMS-Error-thumbnail

Recently I was going through a database programming tutorial that used Northwind Database in code examples, so I thought "No problem, I'll quickly add that database to my SQL Server 2012". I assumed this would be easy but to my surprise that wasn't the case.

In this post I will show you what problems I encountered while trying to install Northwind database and how I finally managed to make it work.

Note:Solution below was tested with the following configuration:

  • Windows 7 Professional 64 bit
  • SQL Server 2012 Express Edition

Step 1 - Getting Northwind Database and examining its content

First we need to download the Database from Microsoft web page. Save the file in your computer and run the installer (SQL2000SampleDb.msi). Installation Wizard will appear. Go through all the steps and in the end it should install the files in the following folder:

c:\SQL Server 2000 Sample Databases

The folder will contain files for two databases:

  1. Pubs sample database
    (contains information about made up book publishing company)
  2. Northwind sample database
    (contains sales data from made up trading company)

Each sample database will have 3 files each with different extensions:

  • .sql
    This one contains script for generating the database. After database is created, the data is stored in MDF and LDF files.
  • .MDF
    This is a primary data file where schema and data of the database is stored.
  • .LDF
    This is a log file where a transaction log information is stored. It keeps track of everything done to the database and can be used to rollback or restore the database.
Note: With SQL Server 2005, Northwind database got replaced with  AdventureWorks database. If you would like to give AdventureWorks a try, you can download it from GitHub.

Step 2 - Installing it on a SQL Server

As already mentioned in previous step, MDF file is where data of the database is located and this file can be used to attach the database to the server, so this is the first thing I tried.

Error while attaching Northwind Database in SQL Server Management Studio
Error while attaching Northwind Database using SSMS

Failed Attempt #1 - attaching database using SQL Server Management Studio 2012 (SSMS)

This tool is available when you install SQL Server 2012. Using Windows 7 you can easily run it by typing sql management in the Start Menu search field.

Tip:If you are using SQL Server 2008, you might not have this tool. You can obtain it from Microsoft Download Center.

When I tried to attach NORTHWND.MDF file, Error Window appeared with following error:

An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.
Error while attaching Northwind Database using Visual Studio Express
Error while attaching Northwind Database using VS Express

Failed Attempt #2 - attaching database using Visual Studio Express

Next I tried to attach it using VS Express. This time I received this warning:

The database file that you are attempting to connect to is not compatible with the current instance of SQL Server. To continue, you must upgrade this database file....Do you want to upgrade the database file now?

I clicked Yes hoping the upgrade will work, but I received another error instead described next.

Errors received with attempt #1 and #2

In both cases, I got the following error when attempting to attach the NORTHWND.MDF file:

Unable to open the physical file C:\SQL Server 2000 Sample Databases\NORTHWND.MDF ... Operating system error 5 (Access is denied), Microsoft SQL Server Error 5120.

I tried to solve this problem by running those applications as an administrator (right-clicking on the application and selecting Run as administrator) but it didn't do much except giving me another error:

Database C:\SQL Server 2000 Sample Databases\NORTHWND.MDF cannot be upgraded because its non-release version (539) is not supported by this version of SQL server...CREATE DATABASE is aborted.

So instead of attaching the database I decided to try to execute instnwnd.sql file.

This attempt was successful and is described in the next section.

What finally worked - executing SQL script in instnwnd.sql file

To execute script I used SQL Server Management Studio 2012 (SSMS) tool that got installed with SQL Server Express. Original script also gave me errors but after a little search on the web I found few different suggestions what to modify in the script, one of which worked.

To successfully execute instnwnd.sql script, run the SQL Server Management Studio and try these steps:

  1. When the SSMS is loaded, Connect to Server window will appear. Click on Connect button. If you are unable to connect to the SQL Server, maybe you are not using Windows Authentication. If that's the case, select SQL Server Authentication  for Authentication and provide the necessary username and password. After successful connection, your SQL Server instance should be listed in the Object Explorer.
  2. Now under File Menu select Open > File.
  3. Find the instnwnd.sql in your computer and select it. Click Open
  4. SQL script should open in the main window.
  5. Do not click on Execute yet. If you do, you will get the following error:
    Could not find stored procedure 'sp_dboption'.
  6. Around the line 20, remove the following two lines:
    exec sp_dboption 'Northwind','trunc. log on chkpt.','true'
    exec sp_dboption 'Northwind','select into/bulkcopy','true'
  7. Replace them with this line as shown below:
    alter database Northwind set recovery simple

    SSMS - Fixing NorthWind script file instnwnd.sql for SQL Server 2012

    Click image to enlarge
  8. All that is left is to execute the script, so click on Execute.

After following above steps you should now have Northwind database on your SQL Server.

Conclusion

With Northwind database you cannot simply attach it or execute its original .sql script on newer versions of SQL servers like 2012 or even 2008. This article demonstrated how to successfully execute instnwnd.sql file with just a small modification in the script.

I hope you have found this post useful. Feel free to drop a comment or share this article on a social networks.

110 Comments

Click HERE to add your Comment
  1. Mark
    August 4, 2013
  2. Mark
    August 4, 2013
  3. Bruce Rush
    August 22, 2013
  4. Mamu
    September 2, 2013
  5. Lucid
    September 11, 2013
  6. Aidan Whitehall
    September 13, 2013
  7. Greg Wilson
    September 16, 2013
  8. Mark Anderson
    October 5, 2013
  9. Bill
    October 9, 2013
  10. Daniel D
    October 31, 2013
  11. john
    November 25, 2013
  12. John
    December 17, 2013
  13. Kishore T
    December 17, 2013
  14. Robert
    December 30, 2013
  15. Daniel
    January 17, 2014
  16. muhammad
    January 20, 2014
  17. Vipin Prabhakaran
    January 23, 2014
  18. Gordon Slysz
    January 28, 2014
  19. saqib
    March 4, 2014
  20. giulio
    April 1, 2014
  21. Alex AK
    April 6, 2014
  22. Mike
    May 27, 2014
  23. juanelos
    May 27, 2014
  24. DG
    June 3, 2014
  25. Retina
    June 4, 2014
  26. James Butler
    June 10, 2014
  27. Ife
    June 12, 2014
  28. Roozmehr Safi
    June 17, 2014
  29. harishankar
    July 8, 2014
  30. Nono
    July 28, 2014
  31. king
    August 22, 2014
  32. mazda
    October 13, 2014
  33. Erhan
    November 8, 2014
  34. Ashok Bhor
    November 22, 2014
  35. Andrew
    December 24, 2014
  36. Irina Max
    December 30, 2014
  37. Mohamed Ragab
    December 31, 2014
  38. Alan
    January 4, 2015
  39. mihelacko
    January 5, 2015
  40. faisal
    January 22, 2015
  41. Jaya
    February 27, 2015
  42. jon illien
    March 5, 2015
  43. Wezik
    March 10, 2015
  44. Francisco Quevedo
    March 17, 2015
  45. Zameer Kakkattu Habeeb Khan
    March 20, 2015
  46. Zak Timms
    March 22, 2015
  47. SQLNewbie
    April 3, 2015
  48. Bennet
    April 11, 2015
  49. Rico
    April 21, 2015
  50. Andre
    June 8, 2015
  51. Dick
    August 5, 2015
  52. Laura
    September 1, 2015
  53. Vladimir
    September 9, 2015
  54. Meirav
    September 19, 2015
  55. Meirav
    September 19, 2015
  56. Peter
    September 23, 2015
  57. novko
    September 29, 2015
  58. Michael
    January 10, 2016
  59. Jason
    February 8, 2016
  60. KBS
    February 29, 2016
  61. M. Algarni
    March 8, 2016
  62. mike
    April 15, 2016
  63. James
    April 19, 2016
  64. kumar
    May 4, 2016
  65. Syed
    May 30, 2016
  66. Akemi
    June 18, 2016
  67. Samuel Ekong
    July 22, 2016
  68. Barry
    July 23, 2016
  69. abbaty48
    August 22, 2016
  70. mahmoud
    October 29, 2016
    • admin
      October 29, 2016
      • mahmoud
        October 29, 2016
  71. Naser
    November 30, 2016
  72. Vibs
    December 24, 2016
  73. Gainwin
    January 23, 2017
  74. L
    January 24, 2017
  75. Bhanu
    February 9, 2017
  76. Giggi
    February 13, 2017
  77. kart
    February 16, 2017
  78. pradeep
    February 22, 2017
  79. Terry Berry
    February 24, 2017
  80. ML
    March 5, 2017
  81. JA
    March 13, 2017
  82. Fayyad
    March 29, 2017
  83. Rod W
    April 14, 2017
  84. Sudha
    May 5, 2017
  85. shubham kamble
    August 21, 2017
  86. oleg
    August 26, 2017
  87. phacharin
    October 22, 2017
  88. Ted
    November 11, 2017
  89. Lavanya Pramod
    November 30, 2017
  90. Mehdi
    January 31, 2018
  91. Juan D
    February 25, 2018
  92. Philip Bradford
    March 26, 2018
  93. Kamil
    March 28, 2018
  94. amon
    April 4, 2018
  95. mohamed elhalwagy
    April 14, 2018
  96. Ramya
    April 28, 2018
  97. Beastlich
    April 30, 2018
  98. DrB
    May 1, 2018
  99. ZaphodB
    June 21, 2018
  100. Star
    July 30, 2018
  101. SM
    September 4, 2018
  102. Yash Jagtap
    November 30, 2018
  103. GrvKush
    February 4, 2019
  104. Daniel
    February 4, 2019
  105. Alejandro Lofredo
    March 19, 2019
  106. Jonty
    May 2, 2019
  107. Habib
    July 7, 2019
  108. Dark Star
    October 13, 2019

Leave a Reply to Lavanya Pramod Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.