Deleting the other schema in SQL 2005

[Former Member]

Dear all,

Currently I am working in the system copy.

I will explain my scenario first then I will ask my queries.

In the source system its an MCOD database(One database three components were running i.e under ECC database , XI and BW were running)

Now we detached the database and copied the log and data files locally in to the NEW BW server.

In target system :

Installed central services instances.

Attached the database.

Ran the STM tool for the conversion.

Now BW server is up and running.

Queries. Since it is attached it also holds the data of other schema. Now in the new server i wnat only the BW schema and not other schemas.

How can i delete the other schema.Please suggest me with your valuble ideas.

Regards

Vijay Kumar

[Former Member · Accepted solution]

Hi Vijay,

In your source system database do you have three separate instance one for ECC, one for XI and one for BI or do you have a single instance with three different database ie. ECC, XI and BI?? It's not possible that you have a single database and which have three different schema (ECC, BI and XI). Please explain.

In first two cases when you executed dettach and attach why do you copy and attach ECC and XI database in the target system when you only want BI? I didn't understand.

Anyway you can delete the database of XI and ECC by logging in to SQL Server Mgmt Studio. Right click on the database and then delete. Same is applicable if you have different schema. It will not affect BI.

Rgds,

SK

[Former Member · Accepted solution]

Dear Sudhip,

Thanks for your suggestion. , Sorry may be I didnt frame my queries properly.

In your source system database do you have three separate instance one for ECC, one for XI and one for BI or do you have a single instance with three different database ie. ECC, XI and BI?? It's not possible that you have a single database and which have three different schema (ECC, BI and XI). Please explain.

Answer: Yes we are having three instances sepreately for each components. This means 3 separate instances with one database.

In first two cases when you executed dettach and attach why do you copy and attach ECC and XI database in the target system when you only want BI? I didn't understand.

Answer: Since it is an MCOD database i am not able to detach the BW system alone. (i.e) Under the ECC database , XI and BW schemas were residing. So I can detach the ECC database only i cant detach the schema alone. Please revert back if you have any queries still.

Anyway you can delete the database of XI and ECC by logging in to SQL Server Mgmt Studio. Right click on the database and then delete. Same is applicable if you have different schema. It will not affect BI.

Thanks for your answer but could you please confirm that if i delete the schema this will turn will reduce the database size?. I mean currently i am having the database size of 180Gb, if i delete the ECC and XI schemas whether this willr educe the size of the databse or not.

Looking forward for your reply.

Regards

Vijay

[Former Member · Accepted solution]

Hi Vijay,

Sorry for the confusion but still I've some doubts. If I can understand your DB structure properly it'll be better for me to answer your queries. MCOD system means instead dbo schema you have schema named with SID.

You said "This means 3 separate instances with one database".

It can't possible to have three separarte instance with one Database.

1. You either have three separate instance each having its own DB like the structure below.

ECC Instance

System DB

master

model

msdb

SAP DB ECC

BI Instance

System DB

master

model

msdb

SAP DB BI

XI Instance

System DB

master

model

msdb

SAP DB XI

2.Or you have one instance with three separate DB like below.

SAP Instance

System DB

master

model

msdb

SAP DB ECC

SAP DB XI

SAP DB BI

Please let me know which structure do you have?

[Former Member · Accepted solution]

Hello Subhadip Kumar

Question : Sorry for the confusion but still I've some doubts. If I can understand your DB structure properly it'll be better for me to answer your queries. MCOD system means instead dbo schema you have schema named with SID.

Answer : I think You have a wrong idea about MCOD system. MCOD means Multiple componets on One database.

Answer : So its possible to have three instance under one database.

So the structure will be as follows,

Three instances (ECC. BW and XI)

One database

master

model

msdb

ECC schema (DBO)

BI Schema

XI Schema

regards

Vijay