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.
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.
When I tried to attach NORTHWND.MDF
file, Error Window appeared with following error:
Failed Attempt #2 - attaching database using Visual Studio Express
Next, I tried to attach it using VS Express. This time I received this warning:
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:
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 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.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:
- Replace them with this line as shown below:
- 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.
Mark
August 4, 2013Great 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
Mark
August 4, 2013Please disregard my previous comment. After disconnecting and reconnecting to the sqlserver I had success attaching the pubs database. Thanks again for the info
Bruce Rush
August 22, 2013Thanks for sharing. that was really usefull
Mamu
September 2, 2013It worked. Thanks for the help.
Lucid
September 11, 2013Thank you very much, made my night.
Aidan Whitehall
September 13, 2013Great article... thanks for the help.
Greg Wilson
September 16, 2013Fantastic! Works great. Much better solution than Microsoft's: using SQL Server 2008...
Mark Anderson
October 5, 2013Thanks so much for publishing this. I had run into the same problem.
Bill
October 9, 2013Thanks so much for an excellent article and solution.
Daniel D
October 31, 2013Thank you sooooo much. It worked perfectly.
john
November 25, 2013most helpful
thank U
John
December 17, 2013THANK GOD! I've been screwing around with this all day. Thanks so much!
Kishore T
December 17, 2013Thanks 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.
Robert
December 30, 2013Very 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.
Daniel
January 17, 2014Thanks a lot!
I was trying to attached it to 2012 version for a good few mins with no succes 😀
Your solution is just perfect 😀
muhammad
January 20, 2014Thanks a lot. It works perfectly.
Vipin Prabhakaran
January 23, 2014Thanks a bunch!!! I faced the problems exactly as you mentioned but was able to resolve it with the steps you gave.
Gordon Slysz
January 28, 2014Excellent walkthrough. You saved me from lots of frustration.
saqib
March 4, 2014Excellent work..Thanks alot
giulio
April 1, 2014THANK YOU SO MUCH!!!
Alex AK
April 6, 2014When 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!!!
Mike
May 27, 2014Thank you. It works with SQL 2014 too.
juanelos
May 27, 2014Perfect, it was working very well, thank you!
DG
June 3, 2014Executing 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!
Retina
June 4, 2014Thank you, I was trying to do it since morning , your solution worked great.
James Butler
June 10, 2014Thank you so much. that REALLY helped me. My teacher and my entire class could not get it working on my computer, but your solution worked perfectly.
thank you
Ife
June 12, 2014Simple solution that easily resolves a major predicament; thanks a bunch!
Roozmehr Safi
June 17, 2014Thanks; it worked great! I tried many other methods but it was only this one that worked.
harishankar
July 8, 2014hi, it works fine. thanks for sharing.
Nono
July 28, 2014Thank you so much!! Great article helped me with error.
king
August 22, 2014Thanks worked great.
mazda
October 13, 2014Perfect! worked on sql2014.
Thanks
Erhan
November 8, 2014Altering the script as you said worked. Thanks.
Ashok Bhor
November 22, 2014Very Useful article...Thanks
Andrew
December 24, 2014Thank you so much!!! It worked!
Irina Max
December 30, 2014Thank you soooo much! finally it is work! How to make it work on Macbook with MySQL?
Mohamed Ragab
December 31, 2014That works thank you .
Alan
January 4, 2015Thanks. That was very useful 🙂
mihelacko
January 5, 2015tnx a lot. It works perfectly.
faisal
January 22, 2015tried but northwind is not there in object explorer but in avaialble databases its there 🙁
Jaya
February 27, 2015very helpful. Thank you so much
jon illien
March 5, 2015Thank you. You helped me
Wezik
March 10, 2015thanks a lot - very useful information 🙂
Francisco Quevedo
March 17, 2015So good... thanks....
Zameer Kakkattu Habeeb Khan
March 20, 2015Thanks, it worked, much appreciated
Zak Timms
March 22, 2015Works exactly as the author says on SQL Server Management Studio 2012 !
SQLNewbie
April 3, 2015I am so grateful for your post. Really saved my day, I am doing Tutorials with W3Schools and they are using Northwing and have been struggling to attach it after downloading it. M enjoying following my lesssons and trying it in my SMS and not on the W3Schools site. You are a Star 🙂
Bennet
April 11, 2015It worked for me. Thank you so much.
Rico
April 21, 2015Thanks! A much better fix than Microsoft suggests!
Andre
June 8, 2015Many, many thanks!
Was not that obvious!
Dick
August 5, 2015I spent hours trying the Microsoft method, and then found your site.
Thank you for this fix.
Laura
September 1, 2015Thank you! This solved my issue.
Vladimir
September 9, 2015Great work ! Thanks from Belgrade - Serbia.
Meirav
September 19, 2015i am still getting a lot of error messages....
Why it doesn't work for me??
I succeeded to install pubs but not Northwind
Meirav
September 19, 2015I succeeded!!!! I succeeded your way!!!! The problem was that I had Northwind MDF file under the DATA file in SQL SERVER FILE. I put it there because I tried to install Northwind by attaching it..... Thank you very very much
Peter
September 23, 2015Excellent Post!!!
It took me almost 3hrs to figure it out how to attach Northwind database on Sql Server 2014.
novko
September 29, 2015It works! Great work!
Michael
January 10, 2016Brilliant, this worked perfectly with SQL Server Express 2014!
Jason
February 8, 2016Thank you and it made a big help!
KBS
February 29, 2016Thank you!
M. Algarni
March 8, 2016Great job! Thanks.
mike
April 15, 2016Thank YOU, thank YOU, thank YOU!!!!
Clear, concise, *AND* it worked exactly as you noted - thank YOU!!!!
James
April 19, 2016Excellent. Worked like a charm!!
Thanks you
kumar
May 4, 2016Thanks.. now it woks perfect...
Syed
May 30, 2016Thank you for writing this up, saved a lot of time. I deeply appreciate this.
Akemi
June 18, 2016Thank you so much! This post helped me a lot...I spent a lot of time trying to attach the databases and also trying to find information on internet but nothing worked, except this post. It was really really helpful!
Samuel Ekong
July 22, 2016Thanks. Worked perfectly!
Barry
July 23, 2016it is helpful to the beginner like me, thanks a lot.
abbaty48
August 22, 2016Wow i like the way you post the problem and the solution.
This help me. Thank you for very useful article.
mahmoud
October 29, 2016Where do I find this code?
admin
October 29, 2016Hi. You need to open
instnwnd.sql
file and then edit the code as shown in the article.mahmoud
October 29, 2016I thank you very much.
Naser
November 30, 2016Thanks, thanks.
Vibs
December 24, 2016Very good. Helped me a lot
Gainwin
January 23, 2017Thank you so much, you made my day!
L
January 24, 2017Thanks, made it work for me.
Bhanu
February 9, 2017It worked fine. I couldnt find the PUBS db after successfully running the script. Re-opening the SSMS, displayed the PUBS DB. Thanks a bunch.
Giggi
February 13, 2017Thank you for your contribution.
kart
February 16, 2017Its 2017 and yet this article is helpful. thanks a lot!
pradeep
February 22, 2017Super, it really helped me lot.
Terry Berry
February 24, 2017Thanks. Your are SQL God!
Terry
ML
March 5, 2017Wow! It worked for me and its 2017.
Four years later!
I may have a chance yet at learning asp.net
JA
March 13, 2017Thanks for your help!
Fayyad
March 29, 2017Thanks very match.
Rod W
April 14, 2017Thanks a lot, it worked.
Sudha
May 5, 2017It worked. Thank you!
shubham kamble
August 21, 2017Thank You very much ...It worked 🙂
oleg
August 26, 2017Thank so much. It really works.
phacharin
October 22, 2017It worked. Thank you!
Ted
November 11, 2017It worked . Thank you very much !!
Great job !
Lavanya Pramod
November 30, 2017This was fantastic !! Worked like a charm.
Very good Post. Thank you so much.
Mehdi
January 31, 2018Thank you for this FANTASTIC article.
Juan D
February 25, 2018Thank you. It solved my issue
Philip Bradford
March 26, 2018This helped a lot!!! Thanks!!
Kamil
March 28, 2018Saved my time. Thanks!
amon
April 4, 2018Thank you very much for sharing.
mohamed elhalwagy
April 14, 2018thanx bro
Ramya
April 28, 2018Thank you so much! I was struggling with this for some time.
Beastlich
April 30, 201899.99% Perfect guide - all you need is to add the following step:
9. Close and restart SSMC and northwind will appear!
[I panicked a bit when it didn't appear after the script execution.]
DrB
May 1, 2018Thanks Beastlich
I was wondering why the solution did not work. I did as you suggest and it worked.
Thanks again
ZaphodB
June 21, 2018Nice work, kind Sir!!! DB installed with no problems. I could not have done it without you! Many thanks!!!!
Star
July 30, 2018WOW!!! I'm so thankful that you uploaded this!
Great help
SM
September 4, 2018Thanks a lot. This really helped.
Yash Jagtap
November 30, 2018A big thank to you.
I followed the exact procedure, which worked perfectly as mentioned.
GrvKush
February 4, 2019Great!! Worked perfectly fine for me. Thanks a ton 😀
Daniel
February 4, 2019This still works! Great article, and perfectly to-the-point without extraneous info.
Alejandro Lofredo
March 19, 2019Thanks for sharing 🙂
Jonty
May 2, 2019Thank you so much... it worked
Habib
July 7, 2019Thanks a lot. You are wonderfull
Dark Star
October 13, 2019QUITE late to the party, but better late than never :-).
Thanks for the information, which worked quite well. I went through roughly the same initial steps you did.
I also threw the PUBS database in there, and had to make the same edit in that one too. Simply did a Ctrl-F for sp_dboption, deleted the offending lines, pasted in the one you provided, and am off to the races. Thanks again.
Robert Beekhuijzen
February 17, 2020Great Instruction!!!
Amongst all others, the only one that works!!
Kostas
November 22, 2020Thank you very much. After reading your article I solved the problem.
SJF
July 6, 2023Thanks! Worked great!!