Re: Remote Proc Restore Fails

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Terry (dontsendmecrud_at_hotmail.com)
Date: 07/08/04 

Date: 8 Jul 2004 11:40:31 -0700

I should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.

dontsendmecrud@hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@posting.google.com>...
> 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

Relevant Pages

  • RE: Log Shipping Monitor
    ... Eliminating the Log Shipping Monitor is equivalent to eliminating all Log Shipping pairs from the Log Shipping system database. ... Deletes the primary server from the log_shipping_primaries table. ... This stored procedure only removes the primary and secondary server from the monitor. ...
    (microsoft.public.sqlserver.server)
  • Log Shipping - SQL Clustering environment
    ... I have configured two node SQL cluster with default instance and SAN as shared disk. ... I have also configured log shipping on primary server which uses maintenance plan to ...
    (microsoft.public.sqlserver.server)
  • Transfering users to secondary database that is in read only mode.
    ... I am having trouble transferring SQL users between primary and secondary ... databases. ... I have tried to use the DTS job but this only ... How can I get users from my primary server to the secondary server if it is ...
    (microsoft.public.sqlserver.server)
  • Log Shipping
    ... I just set up Log shipping and it appears to be working fine however I am ... Now, if I were to restore my primary server, it would use the last nights ... full backup and each subsequent log file since the last backup. ... delete log files older than 5 days. ...
    (microsoft.public.sqlserver.server)
  • Log Shipping
    ... I just set up Log shipping and it appears to be working fine however I am ... Now, if I were to restore my primary server, it would use the last nights ... full backup and each subsequent log file since the last backup. ... delete log files older than 5 days. ...
    (microsoft.public.sqlserver.replication)