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?
Sql-server – SQL Server Multiple Instances but showing the same databases
multiple-instancessql serversql-server-2008ssmswindows-server-2008
Related Topic
- Sql-server – Merge mulitple databases into single database with multiple schemas in SQL Server 2008
- Sql-server – sp_msforeachdb does not list all databases on a SQL Server instance
- Sql-server – Optimizing performance across thousands of SQL Server databases
- Sql-server – Best Way to “Upgrade” SQL Server Express to Web Edition
- Sql-server – Can a Sql Server standard edition backup be restored to web edition
- Windows – multi instance failover clustering (sql server)
Best Answer
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 @@servernameon 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.