Log Shipping using SQL Server Standard Edition | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Log Shipping using SQL Server Standard Edition

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 />[email protected]<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 />
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. HTH Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.