Solved

Backup SQL Server 2000 database on one server remotely from another server.

Posted on 2007-04-05
5
405 Views
Last Modified: 2008-01-09
I have two SQL Servers 2000: ServerA and ServerB. They both linked to each other. On ServerA I created a Stored Procedure spBackupDBOnServerA that backups DB locally; this DB is 16GB in size. On ServerB I created a job that executes spBackupDBOnServerA on ServerA; this job fails with this error:
Executed as user: domain\user_name. Could not relay results of procedure 'spBackupDBOnServerA' from remote server 'ServerA'. [SQLSTATE 42000] (Error 7221)   [SQLSTATE 01000] (Error 7312).  The step failed.
I've searched on the internet, but had no luck.
Any help appreciated.

Thanks.
0
Comment
Question by:zilll
5 Comments
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 18857935
Create folder on the other server and share that folder and give for Read and write permission.

and specify the shared folder path in you backup location...

for ex..
BACKUP DATABASE myDatabase to DISK='\\myRemoteServer\Backup\myDatabaseBackup.bak'
0
 
LVL 16

Accepted Solution

by:
rboyd56 earned 250 total points
ID: 18857936
Open up server properties in your Enterprise Manager,
Go to Connections tab,
Inrease the number in Query time-out field in Remote Server Connections area
0
 
LVL 8

Expert Comment

by:MOA81
ID: 18857952
Dear zilll

I know that SQL Server 2000 can only take backup locally

to take a back on a remote server this can be done in SQL Server 2005

I guess this is your case
0
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 18857964
Hi zilll,
tell me how you are executing this SP "spBackupDBOnServerA" from ServerB to ServerA?
can you post sample code...?




0
 

Author Comment

by:zilll
ID: 18858034
this is the SP which does the actual backup:
BACKUP DATABASE MyDB TO DISK = 'E:\MSSQL\BACKUP\MyDB.BAK' WITH INIT

this is the code how I execute this SP from ServerB in a job:
exec ServerA.DB2.dbo.spBackupDBOnServerA
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Abstract Express Replacement Software 12 28
TSQL query to generate xml 4 35
abs operation in sql server 15 15
Proper Case SQL Command 2 10
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question