I wantto run sql server 2000 and 2005 in 2005 Management Studio Express but don't get the option

I have SQL Server 2000 express working with sql 2005 Management studio express. I loaded SQL Server 2005 and the configuration manager shows that it is running. However when I try to create a new database in Management studio express or restore a 2005 backup it doesn't have that option. What do I need to do to let it see that I have 2005 as an option? I am new to SQL Server and my head is spinning. I am using it as a back-end for MS Access. Thanks!
gaynesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jogosCommented:
The core of your system is a SQL2000 (the old) and you have a backup of a SQL2005-db (the new).

It is likely that 'the old' does not have the knowledge on how to tread 'the new'. So here is your problem, even if you can have a 2005-application (management studio) work on the 2000-engine it does not mean that your 2000 can handle everything what is passed. In this case the internal structure of a backup-file.
0
slam69Commented:
Hi,

not 100% of what you are saying you are doing however my advice would be to back up and detach your dbs from 2000 and then reattach them to the 2005 engine and express studio that way you are keeping theings current.

you can have 2000 and 2005 running on the same machine as sepearet named instances of installs but you wouldnt be able to use one tool to query the others engine if that makes sense.

if you are lookijng to create a new db for 2005 then make sure you ahve installed the 2005 engine, not just the studio
0
gaynesAuthor Commented:
I think I have installed the engine. Is that what shows in the configuration manager? How do I reattach to the 2005 engine? Using what tool? Do I need to install another instance of 2005 Management Studio Express? When I create a new database in there, it doesn't give me the 9.0 option. Do I need to link it somehow?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

slam69Commented:
You shoyuldnt see the 9.0 option.

All yuou need to do from ssms is right click databases and select either attach or restore. the restore function will be necessary if you have already create dthe db you need within the express studio or attach. Once you have right clicked and then click attach you will need to browse to the location of teh old mdf file and attach.

if you use restore you will need to create teh db first and then click restore and navigate to the .bak file

If you get stuck follow down this link for different thigns to try or come back to me

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=162455&SiteID=1

0
jogosCommented:
"Do I need to install another instance of 2005 Management Studio Express? "

If you have both SQL2000 and SQL2005 fully installed and both instaces running it should be obvious in management studio which instance you're connecting to. So the anwer is. you don't need 'another' Sql2005 instance but you need 'A' Sql2005 instance to restore a SQL2005-backup.

Management studio is a client tool so something like a 'Sql2005 Management studio instance' does not exist. You have a SQL2005 instance (the engine as I called it) and there is a client tool 'Management Studio" .  
It probably easier to imagine it on separate machines. You have Server2000 with SQL2000 installed and you have a SERVER2005 where the SQL2005 is installed. On your machine you can perfectly install a management studio without having any database(-instance) on your machine.

Tip:
If you want both a 2000 and a 2005 instance on the same machine it is wise to make it named instances so it always is clear when you want to connect to a database that is on both instances you don't get your application connected to the wrong instance.

0
gaynesAuthor Commented:
I reinstalled Management Studio then I got a dropdown for \express that was the 9.0 instance. It shows up now but the \express server does not show up as an osbc choice on my other machine, just the 8.0 instance. Why would that be?
0
slam69Commented:
It soudns like you are overwriting the instance each time you are reinstalling instead of creating a named instance on teh same machine.

when you do the install are you selecting named instance during teh install process or are you leaving it as default instance.

if you leave it as default it will overwrite the former. if you create a seperate name for both by selecting named then they will co-exist on same server and you can then connect through odbc to either or
0
slam69Commented:
from  http://technet.microsoft.com/en-us/library/ms143219.aspx

On the Instance Name page, select a default or named instance for your installation. If a default or named instance is already installed, and you select the existing instance for your installation, Setup upgrades it and provides you the option to install additional components. To install a new default instance, there must not be a default instance on the computer. To install a new named instance, click Named Instance and then type a unique instance name in the space provided. To install a new named instance side-by-side with an existing instance, click Named Instance and then type a unique instance name in the space provided. For more information about instance naming rules, click Help at the bottom of the page, or see the Instance Name topic in SQL Server 2005 Books Online.
0
gaynesAuthor Commented:
I can add both and see them both, but only the 8.0 shows up on the odbc (listed twice for soem reason)
0
gaynesAuthor Commented:
I shoudl tell you that both are listed on that machines odbc list, just not on a network machine's list.
0
slam69Commented:
Hang on.... in the server name box when configuring the odbc connection manually type in the servername\instancename it wont always appear in the dropdown... im not 100% but i think it only drop downs teh default instances
0
gaynesAuthor Commented:
I tried that, it errors, it does show up on it's own machine
0
slam69Commented:
what error message do you get ? have you tried using the ip address of server instead of the server name.

if teh 8 is working and the instances are on teh same machine you shoudl just need to change teh instance name for connection?
0
gaynesAuthor Commented:
This AM (just turned it on) it shows up on the list but gives me a connect error. I am pulling out my hair...
0
slam69Commented:
what connect error do you get? It could just be permissions related you may need to create the logons within the db,
0
gaynesAuthor Commented:
It all seems to have something to do with network service/local service. The database was stopped, wouldn't start unless I set it to local in the confi manager, it ran on network yesterday, now not in odbc box at all
0
slam69Commented:
check you have all sql server services up and running in services
0
slam69Commented:
and also go to yoru surface area configuration in teh configuration tools of teh start menu

slick for services and connections click the remote connections and ensure local and remote connections radio button is on and you are allowing tcp/ip connections
0
gaynesAuthor Commented:
I have all that set, not it it appearing and disappearing from my list, it must be timing out the connection. I can't devote another wasted day to this. I will spend some time later. Thanks for your help so far. There has got to be some way for this to work.
0
slam69Commented:
could be network traffic its weird that it comes and goes make sure its a devoted sql server not used for anything else and that it isnt running through a hub aside from that i cant explain why it would keep appearing and disappearing
0
gaynesAuthor Commented:
It is my laptop
0
slam69Commented:
lol probably tehre in lies your problem using a laptop that you will be performing tasks regularly on will not be able to support an interactive db for users coming in and out of. you need to be installing it on a machine purely for the process of sql management really with 1gb of ram and 1.6gb of hdd space for the install
0
gaynesAuthor Commented:
This is just for development.
0
slam69Commented:
But you are trying to configure clients to access the machine away from the local client development would not entail this development would involve local traffic only!!!!

Configruing odbc on client machines would come as part of teh depliyment testing but then you would be placing the db on teh server it would reside on for the duration not a lptop
0
jogosCommented:
Sorry but I don't read each comment completly.

"My laptop .... This is just for development. "
To be clear about it.  You installed a SQL2005 as a second instance on your laptop and you are not able to add a database to that SQL2005 instance using the SQL2005 management studio on your own laptop.
Didn't catch if you have made it a named instance.



0
gaynesAuthor Commented:
YOu can just close it since I can run it on my desktop machine now. Thanks!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jogosCommented:
Many tried there best with trying to understand what the problem was or explain where it could be. Initialy 'unable to restore 2005 database' , then ODBC-lists, 'it errors' without a clue which error.....

It's difficult shooting at a moving target.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.