asked on

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

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.

Ved Prakash Agrawal

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'

ASKER CERTIFIED SOLUTION

rboyd56

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MOA81

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

Ved Prakash Agrawal

Hi zilll,
tell me how you are executing this SP "spBackupDBOnServerA" from ServerB to ServerA?
can you post sample code...?

zilll

ASKER

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