Repair Northwind Database in SQL Server Management Studio Express

Hi all,

Long time ago, I downloaded the Northwind and pubs databases from the Microsoft website (I do not remember the details of it) and installed these two databases together into the SQL Server Management Studio Express of my PC (Microsoft Windows XP Pro). I tried to learn an example of using "User Instance" (source code was from a book) on the Northwind database located in my SQL Server Management Studio Express. I just find out that my Northwind database has the title only and no tables at all. If I click on the "+" in front of the "Northwind", I got the following error message:

Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request.(Microsoft SqlServer.Express.SmoEnum)

Additional information:

One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from backup. (Microsoft SQL Server, Error: 5173)..

Please help and tell me how I can repair this Northwind database in my SQL Server Management Studio Express.

Scott Chang

P. S.

I deleted the name 'Northwind' in my SQL Server Management Studio Express, executed the "SQL2005DBScripts\Instnwind" program and I got the following error message: Msg 1802, Level 16, State 4, Line 1

CREATE DATABASE failed. Some file name listed could not be created. Checked related errors.

Msg 5170, Level 16, state 1, Line 1

Cannot create file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\DATA\northwind\northwind.ldf' because it already exist. Change the file path or the file name, and retry the operation.

Msg 15100, Level 16, State 1, Procedure sp_dboption, Line 64

The database 'Northwind' does not exist. Use sp_helpdb to show available database.

Msg 911, Level 16, State 1, Line 1

Could not locate entry in sysdatabases for database 'Northwind'. No entry found with that name. Make sure that name is entered correctly

Delete both Northwind files located at: 'c:\Program Files\Microsoft SQL Server\MSSQL.1\DATA\northwind\'

AND

DELETE any Northwind files located at: 'c:\Program Files\Microsoft SQL Server\MSSQL.1\DATA\'

Then try the (SQL2005DBScripts\Instnwind) installation again.

|||

Hi Arnie, Thanks for your response.

I am not quite sure about your instructions, because I have C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data that has northwnd.ldf, northwnd.mdf and NORTHWND_log.ldf and I do not have 'c:\Program Files\Microsoft SQL Server\MSSQL.1\DATA\northwind\' as you said. Please kindly clarify your instructions for me, so I can do the correct repair work in my PC.

Thanks,

Scott Chang

|||

Scott,

The error message you posted indicated that there was file located in:

Cannot create file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\DATA\northwind\northwind.ldf' because it already exist.

It seems you 'should' delete the Northwind_log.ldf.

Then ATTACH the Northwind.mdf. When you attach the file, verify the filepath of the ldf file, and correct it if necessary.

Apparently, there is some confusion about the path/folder where the northwind files are located. (It is sometimes a confusing process...)