SQL Server Multiple Instances but showing the same databasesMS SQL server: Single or multiple instances?SQL...
Caulking a corner instead of taping with joint compound?
What is better: yes / no radio, or simple checkbox?
Can I solder 12/2 Romex to extend wire 5 ft?
Are small insurances worth it
Using Non-Negative Matrix Factorization (NNMF)
Can a space-faring robot still function over a billion years?
Practical reasons to have both a large police force and bounty hunting network?
When to use mean vs median
Should I use HTTPS on a domain that will only be used for redirection?
How can I highlight parts in a screenshot
Is there a full canon version of Tyrion's jackass/honeycomb joke?
Relationship between the symmetry number of a molecule as used in rotational spectroscopy and point group
Is there a math equivalent to the conditional ternary operator?
How to get the first element while continue streaming?
Is there a way to find out the age of climbing ropes?
Wardrobe above a wall with fuse boxes
Is there a limit on the maximum number of future jobs queued in an org?
Understanding the template
Why do phishing e-mails use faked e-mail addresses instead of the real one?
Where is this quote about overcoming the impossible said in "Interstellar"?
Why would the IRS ask for birth certificates or even audit a small tax return?
How does insurance birth control work?
1970s scifi/horror novel where protagonist is used by a crablike creature to feed its larvae, goes mad, and is defeated by retraumatising him
Why are special aircraft used for the carriers in the United States Navy?
SQL Server Multiple Instances but showing the same databases
MS SQL server: Single or multiple instances?SQL Server 2005 and 2008 instances on the same machineMerge mulitple databases into single database with multiple schemas in SQL Server 2008sp_msforeachdb does not list all databases on a SQL Server instanceOptimizing performance across thousands of SQL Server databasesMultiple SQL Server Databases and Single/Multi Server SetupCan a Sql Server standard edition backup be restored to web editionTransferring 3 SQL databases from both SQL Express 2005 and Standard to a new physical servermulti instance failover clustering (sql server)from security perspective one instance with Multiple Databases or multiple instance for multiple clients
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?
windows-server-2008 sql-server sql-server-2008 ssms multiple-instances
edited Nov 17 '14 at 10:58
Falcon Momot
22.7k104879
asked Nov 4 '14 at 14:28
DangeRussDangeRuss
7229
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 1 more comment
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?
windows-server-2008 sql-server sql-server-2008 ssms multiple-instances
edited Nov 17 '14 at 10:58
Falcon Momot
22.7k104879
asked Nov 4 '14 at 14:28
DangeRussDangeRuss
7229
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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,1234TRAIN and the second one is servername,1234UAT - 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
|
show 1 more comment
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?
windows-server-2008 sql-server sql-server-2008 ssms multiple-instances
edited Nov 17 '14 at 10:58
Falcon Momot
22.7k104879
asked Nov 4 '14 at 14:28
DangeRussDangeRuss
7229
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?
windows-server-2008 sql-server sql-server-2008 ssms multiple-instances
windows-server-2008 sql-server sql-server-2008 ssms multiple-instances
edited Nov 17 '14 at 10:58
Falcon Momot
22.7k104879
asked Nov 4 '14 at 14:28
DangeRussDangeRuss
7229
edited Nov 17 '14 at 10:58
Falcon Momot
22.7k104879
asked Nov 4 '14 at 14:28
DangeRussDangeRuss
7229
edited Nov 17 '14 at 10:58
Falcon Momot
22.7k104879
edited Nov 17 '14 at 10:58
Falcon Momot
22.7k104879
edited Nov 17 '14 at 10:58
Falcon Momot
22.7k104879
22.7k104879
asked Nov 4 '14 at 14:28
DangeRussDangeRuss
7229
asked Nov 4 '14 at 14:28
DangeRussDangeRuss
7229
asked Nov 4 '14 at 14:28
DangeRussDangeRuss
7229
7229
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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,1234TRAIN and the second one is servername,1234UAT - 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
|
show 1 more comment
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,1234TRAIN and the second one is servername,1234UAT - 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
Can you give an example of the instance names you are using?
– Bart De Vos
Nov 4 '14 at 14:29
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,1234TRAIN and the second one is servername,1234UAT - 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
Yes, so the first instance is servername,1234TRAIN and the second one is servername,1234UAT - 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
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
@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
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
|
show 1 more comment
1 Answer
1
active
oldest
votes
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.
answered Nov 4 '14 at 15:56
Ben ThulBen Thul
2,4311219
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "2"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f641912%2fsql-server-multiple-instances-but-showing-the-same-databases%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 4 '14 at 15:56
Ben ThulBen Thul
2,4311219
add a comment |
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.
answered Nov 4 '14 at 15:56
Ben ThulBen Thul
2,4311219
add a comment |
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.
answered Nov 4 '14 at 15:56
Ben ThulBen Thul
2,4311219
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.
answered Nov 4 '14 at 15:56
Ben ThulBen Thul
2,4311219
answered Nov 4 '14 at 15:56
Ben ThulBen Thul
2,4311219
answered Nov 4 '14 at 15:56
Ben ThulBen Thul
2,4311219
answered Nov 4 '14 at 15:56
Ben ThulBen Thul
2,4311219
2,4311219
add a comment |
add a comment |
Thanks for contributing an answer to Server Fault!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f641912%2fsql-server-multiple-instances-but-showing-the-same-databases%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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,1234TRAIN and the second one is servername,1234UAT - 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