Northwind Sample Database for 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 the 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 Northwind Database. Save the file on 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 a book publishing company) - Northwind sample database
(contains sales data from a 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:Besides Northwind database, there is also 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 the previous step, the 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.
Failed Attempt #1 - attaching database using SQL Server Management Studio (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 don't have this tool, you can obtain it from here.
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.
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 the script I used SQL Server Management Studio 2012 (SSMS) tool that got installed with SQL Server Express. The original script also gave me errors but after a little search on the web, I found a few different suggestions on what to modify in the script, one of which worked.
To successfully execute the 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.sqlin 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 image to enlarge
- All that is left is to execute the script, so click on Execute.
After following the above steps you should now have the Northwind database on your SQL Server.
Conclusion
With the 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.
Northwind Sample Database for Sql Server 2012
Source: https://www.howtosolutions.net/2013/07/solving-install-northwind-database-on-sql-server-problem/