Move data from one machine to another

[sunnyjassal]

Hello,

I have a procedure (see below) that moves copies data from one DB to another. This procedure is written in DB1 and is excecuted as a Maintenance plan. Both DB1 and DB2 are on the same server. I want to change this and move DB2 on another machine. How can I modify the below procedure to connect to a different machine and do the same process?

Thanks for your help in advance.


ALTER PROCEDURE [dbo].[MoveData]

AS
BEGIN

drop table DB2..Billing
select * into DB2.dbo.Billing from
DB1.dbo.Billing
END

 

[jbenson001]

Create a linked server, then:

ALTER PROCEDURE [dbo].[MoveData]
    
AS
BEGIN

drop table NewServerName.NewDatabasename.dbo.Billing
select * into NewServerName.NewDatabasename.dbo.Billing
from
   DB1.dbo.Billing
END

Jim

 

[sunnyjassal]

Sorry but how do you create a linked server?

 

[jbenson001]

No problem.

Open Enterprise Manager. Open the source server(In the treeview on the left), in your case, DB1.

Click the "+" next to the Security folder.

Right click on "Linked Servers", Click "New Linked Server.

Click the SQL Server radio button.

In the Linked Server textbox, type the name of the new server.

Click the Security Tab. Choose the way you want to connect to the new server.

Click OK.

Jim

 

[sunnyjassal]

Thanks for your help. I am using SQL Server 2005 and was able to find link database section. I selected SQL Server Option and put in the IP address for the SQL Server and the connection was established. But how do i select an alias for the IP address??

It wont allow me to do 223.223.223.DB2.Billing

 

[sunnyjassal]

I created another instance of linked server by not selecting SQL Server Option

for linked name i used BackupDB, for datasource I used the IP address and it created the link.

But now I am getting the following error:

BACKUPDB does not exist when I try to execute the following:

select * into BACKUPDB2.DB2.Billing from
DB1.dbo.Billing

 

[sunnyjassal]

OOPS ... the select statement is

select * into BACKUPDB.DB2.Billing from
DB1.dbo.Billing

and not

select * into BACKUPDB2.DB2.Billing from
DB1.dbo.Billing

Just mistyped it in the forum

I have tried all different alterations but I still keep on getting BACKUPDB does not exist

 

[jbenson001]

Thanks for your help. I am using SQL Server 2005 and was able to find link database section. I selected SQL Server Option and put in the IP address for the SQL Server and the connection was established. But how do i select an alias for the IP address??

Instead of the IP address, use the server name.