Copy Server problems

[greg]

I've 7 regional production servers that I want to copy to another server
every night. Let's call the source production servers 1 through 7, and the
destination X.

Server 1 has links to server 2 through 6. Why? I'm not sure, I'm trying to
find out so I can delete them if possible :)

So, I made a DTS on X to copy a database from 1 to X. However, it throws an
error. Logging the DTS I get, I replaced the server name with *SERVER 1*.

Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 42000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Could
not find server '*SERVER 1*' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
Step Error code: 80041C22
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:1131


So until I can delete these links (not sure how to find out if they're being
used), how can I fix this? Do I need to make a link on server X as well?

Thanks,
Greg

[Mark Han[MSFT]]

Hi Gerg,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

Based on your description, you would like to copy databases from server 1-7
to server X. Is it correct? If I misunderstand anything, please let me
know. That will help us better understand the issue.

In order to address your concerns, I would like to explain the following
I) regarding to your description, some linked servers have created on the
server 1. We can find them by the following step
1. use SSMS connect to the server 1
2. expend server object->linked servers.

2 Based on the error message, the issue might to be related to how the DTS
is created. and it seems to require us to add server 1 as a linked server
on server X.

3 if you don't want to setup a linked server on server X, it is suggested
to use "COPY Database Wizard". On the server 1, right click the database
which you want to copy to server X ->Task->COPY Database

About "Copy Database Wizard", there is an article to share with
you:http://msdn.microsoft.com/en-us/library/ms183462(SQL.90).aspx

Hope the above helpful.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: ***@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Mark Han[MSFT]]

Hi Gerg,

This is Mark. I'm writing to follow the issue.

If I can assist you anything related to the issue, please let me know.

I look forward to hearing from you.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: ***@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and c

[greg]

Hi Mark.

Indeed it would appear there are linked servers. I wanted to make this
process as transparent as possible. So, perhaps I will add the links to
server X.

Is it possible to add the links yet have them read-only or something like
that so I can ensure server X never actually *uses* the links? (But they will
be defined to satisfy the DTS)

Thanks,
Greg

Post by Mark Han[MSFT]
Hi Gerg,
Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.
Based on your description, you would like to copy databases from server 1-7
to server X. Is it correct? If I misunderstand anything, please let me
know. That will help us better understand the issue.
In order to address your concerns, I would like to explain the following
I) regarding to your description, some linked servers have created on the
server 1. We can find them by the following step
1. use SSMS connect to the server 1
2. expend server object->linked servers.
2 Based on the error message, the issue might to be related to how the DTS
is created. and it seems to require us to add server 1 as a linked server
on server X.
3 if you don't want to setup a linked server on server X, it is suggested
to use "COPY Database Wizard". On the server 1, right click the database
which you want to copy to server X ->Task->COPY Database
About "Copy Database Wizard", there is an article to share with
you:http://msdn.microsoft.com/en-us/library/ms183462(SQL.90).aspx
Hope the above helpful.
Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Mark Han[MSFT]]

Hi Greg,

Thank you for the update.

I understand that you would like to add server 1 as a linked server on
server X.

in order to address your concerns, I would like to explain the following
1 Please descrip the following in detail. What you meaning "use"?
I can ensure server X never actually *uses* the links

2 when creating linked server, it is allowed for us to grant different user
different rights, we can just give th read role to the user who will visit
the linked server.

3 when using DTS, we can use another user who have read/write role on the
databse in the linked server.

Hope the above helpful.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: ***@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[greg]

Hi Mark.

My understanding of linked servers is it can allow remote calls for stored
procedures.

I want to make sure that there's no triggers or anything that would invoke a
remote call of a stored procedure on Server 1, from Server X. Server X is a
development box, we're just trying to consolidate all databases here.

Hence I am curious about a "read only" mode. So the link is present, but
perhaps I disable any remote stored procedure calls, and grant only SELECT on
all the remote tables? Any INSERT UPDATE DELETE or executes issued from
Server X to Server 1 would fail.

Post by Mark Han[MSFT]
Hi Greg,
Thank you for the update.
I understand that you would like to add server 1 as a linked server on
server X.
in order to address your concerns, I would like to explain the following
1 Please descrip the following in detail. What you meaning "use"?
I can ensure server X never actually *uses* the links
2 when creating linked server, it is allowed for us to grant different user
different rights, we can just give th read role to the user who will visit
the linked server.
3 when using DTS, we can use another user who have read/write role on the
databse in the linked server.
Hope the above helpful.
Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Mark Han[MSFT]]

Hi Greg,

Thank you for the reply. To create a linked server, we can collect the
information from the linked server and local server togther at same time.

About your concern, it is no need to created linked server on Server X. it
is more convenient to you to copy the database from server 1 to server X
and then we can set the appropriate role to appropriate user.

Besides, about how to copy databse from one server to another, the detail
steps are in my previous response.

Hope the above helpful.

Best regards,
Mark Han
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
====================================================

[greg]

Thanks Mark.

Okay, I am now using the Transfer Database task instead. I enter the source,
destination, and select a single database to copy.

When I click on the File Locations tab, Enterprise Manager freezes. Any ideas?


Thanks,
Greg

Post by Mark Han[MSFT]
Hi Greg,
Thank you for the reply. To create a linked server, we can collect the
information from the linked server and local server togther at same time.
About your concern, it is no need to created linked server on Server X. it
is more convenient to you to copy the database from server 1 to server X
and then we can set the appropriate role to appropriate user.
Besides, about how to copy databse from one server to another, the detail
steps are in my previous response.
Hope the above helpful.
Best regards,
Mark Han
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
====================================================

[greg]

Sorry Mark - I cannot use the transfer database task since it detaches the
database during the execution. This is done live from our production database.

Greg

Post by Mark Han[MSFT]
Hi Greg,
Thank you for the reply. To create a linked server, we can collect the
information from the linked server and local server togther at same time.
About your concern, it is no need to created linked server on Server X. it
is more convenient to you to copy the database from server 1 to server X
and then we can set the appropriate role to appropriate user.
Besides, about how to copy databse from one server to another, the detail
steps are in my previous response.
Hope the above helpful.
Best regards,
Mark Han
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
====================================================

[Mark Han[MSFT]]

Hi Greg,

Thank you for the update.

For your convenience, I list the detail steps to use Copy Database Wizard.
1On the server 1, right click the database
2 which you want to copy to server X ->Task->COPY Database

About "Copy Database Wizard", there is an article to share with
you:http://msdn.microsoft.com/en-us/library/ms183462(SQL.90).aspx

If you would like to add linked server(server 1) on the server X, please
use sp_addlinkedserver. For more information, please refer
:http://msdn.microsoft.com/en-us/library/ms190479.aspx

Besides,if you would like the user on the server X just can do "select" on
the linked server, you need map the local login with alogin accout with
"read only" role on the databse when you add linked server login,
There is 2 artilces to share with you:
Database level role: http://msdn.microsoft.com/en-us/library/ms189121.aspx
sp_addlinkedsrvlogin
(Transact-SQL):http://msdn.microsoft.com/en-us/library/ms189811.aspx

Hope the above helpful.
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: ***@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Mark Han[MSFT]]

Hi Greg,

This is Mark. I'm writing to follow up the post.

I haven't heard from you for a few days. Does my explanation addrress your
concern?

I look forward to your update.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: ***@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================