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?

  • Can you give an example of the instance names you are using? – Bart De Vos Nov 4 '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 4 '14 at 14:33
  • 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 4 '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 4 '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 4 '14 at 14:38
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.