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:
- Pubs sample database
(contains information about made up book publishing company)
- Northwind sample database
(contains sales data from made up trading company)
Each sample database will have 3 files each with different extensions:
This one contains script for generating the database. After database is created, the data is stored in MDF and LDF files.
This is a primary data file where schema and data of the database is stored.
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.
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 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.
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 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
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:
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
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
Click to Enlarge
- 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.
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.