SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


create another instance but will be the same DB name on same server


create another instance but will be the same DB name on same server

Author
Message
poratips
poratips
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 653
Hi,
I need to create another instance but will be the same DB name on same server, is it possible?

1) i have server called Server1 and it has instance called (DBDEV) and it has different DB like DB1, DB2
2) My Data File and Log file is on D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
3) Now i need to create another Instance so they can use using as IP address
4) they wants same DB name as in my exising Instance
5) We have Sql 2005 on Windows m/c

Could you please guide me as is it possible or not and how can i do this?
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10094 Visits: 5314
poratips (7/25/2013)
Hi,
I need to create another instance but will be the same DB name on same server, is it possible?

1) i have server called Server1 and it has instance called (DBDEV) and it has different DB like DB1, DB2
2) My Data File and Log file is on D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
3) Now i need to create another Instance so they can use using as IP address
4) they wants same DB name as in my exising Instance
5) We have Sql 2005 on Windows m/c

Could you please guide me as is it possible or not and how can i do this?


You can have 2 instances on the same IP with the same databases BUT the instance name will have to be different, your current setup is DBDEV, you can create an instance with any other valid name.

ServerName\DBDEV
ServerName\DBDEV2

They are two entirely separate SQL servers so having the same Database name on each is just fine. They will be at different places on disk as well. Does this make sense?

CEWII
poratips
poratips
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 653
Thanks Elliott for quick response as i need to do it today.

1) When i install sql 2005 on same server, i can leave the default instalaltion, right?
2) We have only two drive c and D and on D drive we have data file and log file
Is it my data file name having an issue if i specify the directory D:\?


You can have 2 instances on the same IP with the same databases BUT the instance name will have to be different, your current setup is DBDEV, you can create an instance with any other valid name.

ServerName\DBDEV
ServerName\DBDEV2

They are two entirely separate SQL servers so having the same Database name on each is just fine. They will be at different places on disk as well. Does this make sense
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10094 Visits: 5314
poratips (7/25/2013)
Thanks Elliott for quick response as i need to do it today.

1) When i install sql 2005 on same server, i can leave the default instalaltion, right?
2) We have only two drive c and D and on D drive we have data file and log file
Is it my data file name having an issue if i specify the directory D:\?


I know that SQL 2008 and above knows about other installs on the server, I THINK SQL 2005 does as well, it should make you chose a different instance name which will cause it to install in a different path. This is all good. Under NO circumstances use the same path for user databases between the two instances. Each instance should use different paths. The instances can share the same drive, but keep in mind they will fight each other for disk resources.

CEWII
poratips
poratips
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 653
Thanks Elliott once again.

I have already default instance and we called by ip or DEVDB then iinstalled sql 2005 and created another instance as named instance - STG so it's called DEVDB\STG becuase sql server can have only one default instance.

By mistake, i forgot to change the default location for files so all system DB created and using C drive.

How can I change the location? Should i leave the system DB there and create another MSSQl directory on D:\ drive like files - D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL and when i create/Migrate new DB then change the .mdf and .ldf location to new path?

If i migrate DB then it will go to old location where my system DB files are, right?
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10094 Visits: 5314
You can set the default location for user databases in management studio. There are good articles online for movie the system databases. It would be better to use one of those than for me to try and explain it. The system databases do not need to be in the same place as the user databases, there is no requirement for that.

CEWII
poratips
poratips
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 653
Ok, Then I will just leave the System DB on C and will change it to D drive for USer DBs when I will do the Migration/creation of new DB.



Thank you!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search