I can connect to a remote SQL 2008 Server instance, but I cannot see all the databases in the instance using SQL Server 2008 Management Studio.

I suspect that my problem has something to do with not all database in the remote instance having the same version. For example, I "upgraded" a 2005 database to 2008 by doing a backup from 2005 and importing it into 2008.

When I realized that this was not one of the database that I could see from my other PC, I noticed that the compatibility level of the imported was still 2005, so I changed it to 2008. Still I could not see the database.

On that remote server, the instance node is named "sql2008", and is "Version 10".

When I connect to the sql2008 remote instance from my local PC, the connection is shown locally as being a "SQL Server Version 8.0" instance.

I suspect that locally, I am only being shown databases that are somehow in the remote 2008 instance but have not been upgraded.

I guess I don't know what constitutes an upgraded database and I don't know how to connect to see all the databases, even if this requires multiple connections from the source PC.

The local machine is Windows 7 Ultimate. The remote host is Windows XP Pro.

Related -not being able to see the SQL Server 2008 instance.

Update:

This is what I see on the remote server using Mgt Studio, namely, a SQL 2008 instance and a 2000 instance.

When I log开发者_如何学Python onto the SQL 2008 instance and perform the following query

SELECT @@VERSION As Version, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ComputerNamePhysicalNetBIOS, SERVERPROPERTY('ServerName') As ServerName, SERVERPROPERTY('MachineName') As MachineName

The result re-confirms that the 2008b instance shown above with a version of 10 is a 2008 instance:

    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) 

USPL-DOKMANC2   USPL-DOKMANC2\SQL2008B  USPL-DOKMANC2

OK, now from my LOCAL box, I used SQL Mgt Studio to try and connect to USPL-DOKMANC2 (the remote box.)

alt text http://content.screencast.com/users/Dokmanc/folders/Jing/media/ec146f56-5651-4968-9286-82508dc5d3b2/2010-05-22_1426.png

...I connect to the remote w/o the port I get this error:

alt text http://content.screencast.com/users/Dokmanc/folders/Jing/media/d3c5bc46-c286-4708-a2f5-282fc76124cb/2010-05-22_1427.png

But if I enter the DEFAULT port, I am able to connect. Note that the SQL2008 connection that I just added appears as being version "8" (SQL 2000). alt text http://content.screencast.com/users/Dokmanc/folders/Jing/media/ea80af5e-4a1f-4417-9327-0b76476b4d14/2010-05-22_1426.png

I seem to be connecting to my SQL2000 instance again even though I specified in my connection params to connect to SQL2008b, a 2008 instance.

I can't seem to see my remote 2008 instance on uspl-dokmanc2. I keep getting my 2000 default instance databases showing up under sql2008b.

2nd Update:

Based on excellenf feedback below that SQL is most likely disregarding the name of the instance when tryingt o connect and determining the instance from the port specified. Now I just need to know the port that the SQL2008b instance is running on. I expected that SQL Mgt Config would tell me. The port is blank. Does this imply a default port? What would it be for 2008, not ethat I already have another 2008 instance, so if they all have to have separate ports, the normal 2008 default is probably not it.

Here's what I see in SQL Mgt Config for the 2008b instance. No port mentioned:

The remote instance (8.0) is a SQL Server 2000 instance

There is no such concept as "database version" that relates to visibility or permissions: every DB on a given server (version, SP and hotfix included) is at some internal version. This is why you can't restore or attach a SQL Server 2008 DB onto SQL Server 2005

I would run this to get the full details

SELECT
   @@VERSION,
   SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
   SERVERPROPERTY('ServerName'),
   SERVERPROPERTY('MachineName')

For example, you may have a client alias that means you're connecting to the wrong server.

Also, you have to explicitly DENY ANY VIEW DATABASE TO public to "hide" databases

Are you sure your 2008 instance uses 1433 port? MS SQL seem to disregard a name of a instance and connect with the given port in most cases. Use the Configuration Manager to check the port.

Also, check if the SQL Browser is available from the remote machine - you'll have to add 1434 on UDP to your firewall settings. Remember to make sure that the SQL Browser is running on your server of course. See: http://support.microsoft.com/kb/914277