SQL Server Performance

Log Shipping using SQL Server Standard Edition

Discussion in 'SQL Server Log Shipping' started by noobie, Jul 29, 2004.

  1. noobie New Member

    Using SQL Server Standard Edition, I am trying to restore transaction logs to a standby server. The transaction log backups are done every 30 minutes to a backup device (appended). I am trying to write SQL code that determines what the file number of the latest transaction log is, then restore it to the standby server. The code gets an error but will work when I run it from SQL Query Analyzer but the code errors out and will not restore the latest log when I run it as a job. <br />The error:<br />Executed as user: COUGARSSQLAdmin_Juxa. Cannot find file ID 8 on device 'IsisEDMSLog'. [SQLSTATE 42000] (Error 403<img src='/community/emoticons/emotion-11.gif' alt='8)' /> RESTORE FILELIST is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.<br /><br />The following is the code I#%92m using:<br /><br />DECLARE @FILE int<br />DECLARE @GetOut bit<br /> <br />SET @FILE = 1<br />SET @GetOut = 0<br /> <br />WHILE ( @@ERROR = 0 AND @GetOut = 0 )<br />BEGIN <br />RESTORE FILELISTONLY<br />FROM IsisEDMSLog<br />WITH<br />FILE=@FILE<br /> <br /> IF ( @@ERROR = 0 ) <br /> BEGIN<br /> SET @FILE = @FILE + 1<br /> END<br /> ELSE<br /> BEGIN<br /> SET @FILE = @FILE - 1<br /> SET @GetOut = 1<br /> END<br />END<br /><br />RESTORE LOG EDMS<br />FROM IsisEDMSLog<br />WITH<br />File = @FILE,<br />STANDBY = 'E:Microsoft SQL ServerMSSQLBACKUPBackupEDMSundo_edms.ldf',<br />MOVE 'EDMS_Data' TO 'E:Microsoft SQL ServerMSSQLDataEDMS_Data.mdf', <br />MOVE 'EDMS_Data_2' TO 'E:Microsoft SQL ServerMSSQLDataEDMS_Data_2_Data.mdf',<br />MOVE 'EDMS_Folders' TO 'E:Microsoft SQL ServerMSSQLDataEDMS_Folders_Data.mdf',<br />MOVE 'EDMS_Log' TO 'L:EDMS_log.ldf'<br /> <br />PRINT @@ERROR<br />RETURN<br />GO<br />
  2. satya Moderator

    When a database is backed up to multiple disk files, and one of the files in the backup set is deleted and then used again in the same backup set to back up a database, a restore of the database using this backup set may fail with the above message.

    In order to function the schedule to log shipping restore the full database backup from source server to target server and restart the log backup sequence.

    http://www.sql-server-performance.com/sql_server_log_shipping.asp for information on performing own log shipping.


    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page