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

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 CodePlex.

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:

  • 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.
  • Now under File Menu select Open > File.
  • Find the instnwnd.sql in your computer and select it. Click Open
  • SQL script should open in the main window.
  • Do not click on Execute yet. If you do, you will get the following error:
    Could not find stored procedure ‘sp_dboption’.
  • 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'
  • 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

  • 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.

30 thoughts on “SQL : How to solve Northwind Database Install Error in SQL Server 2012

  1. Great article. I tried to do the same for pubs but encounter:
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\pubs.mdf’. (Microsoft SQL Server, Error: 5123)
    A search came up blank

    I am hoping you might have an idea of how to modify the
    instpubs.sql file to achieve the required outcome. Thanks

  2. Please disregard my previous comment. After disconnecting and reconnecting to the sqlserver I had success attaching the pubs database. Thanks again for the info

  3. Thanks a lot for help. Am beginning to learn SQL today, wish me all the best.

    Please let me know if there are any specific tutorials of yours so that I can go through.

  4. Very helpful. I found a Northwind database that I did not have to alter which I installed on one computer but had so much difficulty finding the same address from another location that i tried your method. If worked great and no issues at all.

    Thanks again.

  5. When I ran into the issue and discovered compatibility issues between 2012 and 2000 I realized it was going to be stressful and time consuming. However, when I ran into this post instructions were quite simple so I gave it a try and IT WORKED. what a relief ….thank you!!!

  6. Executing the sql version (after applying the suggested edit) returned a bunch of errors. I *finally* got the thing to install by doing the following:

    1) Right click ‘Databases’ and click ‘Attach’
    2) Click ‘Add’ and choose ‘northwind.mdf’
    a) Notice under ‘Database details:’ the MDF and LDF file extensions are listed – click on the LDF file, then click ‘Remove’
    3) Click ‘OK’

    This method should work for the PUBS sample database too…

    Hope this helps!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

CommentLuv badge