Remote Proc Restore Fails
From: Terry (dontsendmecrud_at_hotmail.com)
Date: 07/08/04
- Next message: Dan D.: "Re: using "with readuncommitted" in a query"
- Previous message: Shawn Brock: "Occasional Authentication Failure"
- Next in thread: Terry: "Re: Remote Proc Restore Fails"
- Reply: Terry: "Re: Remote Proc Restore Fails"
- Messages sorted by: [ date ] [ thread ]
Date: 8 Jul 2004 06:35:52 -0700
I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
Terry
- Next message: Dan D.: "Re: using "with readuncommitted" in a query"
- Previous message: Shawn Brock: "Occasional Authentication Failure"
- Next in thread: Terry: "Re: Remote Proc Restore Fails"
- Reply: Terry: "Re: Remote Proc Restore Fails"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
