0

I am running SQL Server 2008 R2 Standard Edition and need to have multiple instances which I have installed. However, when I restore a database onto one instance, it is showing up on the other instance as well... What I am trying to do is have 2 separate instances on one server but each instance having its own set of databases. Why would both instances be showing the the same databases?

Falcon Momot
  • 25,244
  • 15
  • 63
  • 92
DangeRuss
  • 217
  • 4
  • 10
  • Can you give an example of the instance names you are using? – Bart De Vos Nov 04 '14 at 14:29
  • Yes, so the first instance is servername,1234\TRAIN and the second one is servername,1234\UAT - so the '1234' is the port number followed by the instance name \UAT or \TRAIN. I've tested by removing a database and re-adding it and it pops up on both instances and removed from both instances. I also updated a table to test and it also updated the table in the other instances database table... so they are the same database. I was certain having multiple instances would allow separate databases. – DangeRuss Nov 04 '14 at 14:33
  • 1
    ah, 1234 is NOT the port number. If it would be - then you would be connected to the SAME instance as instances have separate port numbers. I would say you accidentally connect to the same instance all over due to that. – TomTom Nov 04 '14 at 14:34
  • @DangeRuss Oh, and there is a website for dbas here - generally questions about database administration belong there. dba.stackexchange.com – TomTom Nov 04 '14 at 14:36
  • Ah ha! Wonder why it defaulted to that? I have an initial default instance installed using a different port number (its not really 1234 but you get what I mean). So when I logged into SSMS, it showed the instances but on those port numbers for some reason... when I check configuration manager, I see that those are NOT the correct ports. So its seems I was just logging in wrong! Easy enough, thank you very much! – DangeRuss Nov 04 '14 at 14:38
  • @TomTom - just saw the comment about the DBA site, thank you! I will use that one from now on. – DangeRuss Nov 04 '14 at 15:05

1 Answers1

1

Based on the above comments, you're logging into the same instance. If you specify the port number in the connection string, the instance name will be ignored. To prove this to yourself, run select @@servername on both of your connections and you'll see that it returns the same value.

In order to fix this, either specify the correct port for both instances (at least one of them is incorrect right now as only one server can listen on a given port at a time) or just use the instance names (i.e. omit the port number) and let the SQL Browser service do the port lookup for you.

Ben Thul
  • 3,024
  • 17
  • 24