SQL-BACKUP Logshipping

HansBleijendaal

I have problems with logshipping. After restore of the database, the logshipping is started. The first 2 restore-file are restored without problems. At time 15:25 (red color) we espected a resore-file from the "MASTERSERVER", but there was no file. After that the logsipping is out-of sequence.

How can I prevent this?
I did not get an error on the job-execution; The restore is scheduled by an SQL-job, but the job self did not fail

Hans Bleijendaal
hbleijendaal@admworld.com




9-6-2005 15:19:35: SQL Backup 3.1.0, (c) Red Gate Software Ltd 2004 - 2005
9-6-2005 15:19:35:
9-6-2005 15:19:35: Restoring database wpi_koog_db2 from "D:\SQL Data\tlogs_restore\FULLBackup\wpi_koog_db2.sqb"
9-6-2005 15:19:35:
9-6-2005 15:19:35: RESTORE DATABASE [wpi_koog_db2] FROM DISK = 'D:\SQL Data\tlogs_restore\FULLBackup\wpi_koog_db2.sqb' WITH STANDBY = 'D:\SQL Data\MSSQL\Data\wpi_koog_db2_undo.ldf', MOVETO = 'D:\SQL Data\tlogs_restore\FULLBackup\processed\'
9-6-2005 15:19:35:
9-6-2005 15:19:35:
9-6-2005 15:19:37: Moved wpi_koog_db2.sqb to \SQL Data\tlogs_restore\FULLBackup\processed\.

Processed 3736 pages for database 'wpi_koog_db2', file 'wpi_koog_db2_Data' on file 1.
Processed 1 pages for database 'wpi_koog_db2', file 'wpi_koog_db2_Log' on file 1.
RESTORE DATABASE successfully processed 3737 pages in 0.202 seconds (151.516 MB/sec).


9-6-2005 15:23:00: SQL Backup 3.1.0, (c) Red Gate Software Ltd 2004 - 2005
9-6-2005 15:23:00:
9-6-2005 15:23:00: Restoring transaction logs for database wpi_koog_db2 from "D:\SQL Data\tlogs_restore\*wpi_koog_db2*.sqb"
9-6-2005 15:23:00:
9-6-2005 15:23:00: Processing log group 0 ...
9-6-2005 15:23:00: \SQL Data\tlogs_restore\LOG_(local)_wpi_koog_db2_20050609 152302.sqb
9-6-2005 15:23:00: RESTORE LOG [wpi_koog_db2] FROM DISK = 'D:\SQL Data\tlogs_restore\LOG_(local)_wpi_koog_db2_20050609 152302.sqb' WITH STANDBY = 'D:\SQL Data\MSSQL\Data\wpi_koog_db2_undo.ldf', MOVETO = 'D:\SQL Data\tlogs_restore\processed\'
9-6-2005 15:23:00:
9-6-2005 15:23:00:
9-6-2005 15:23:02: Completed.
9-6-2005 15:23:02:
9-6-2005 15:23:02: Moved LOG_(local)_wpi_koog_db2_20050609 152302.sqb to \SQL Data\tlogs_restore\processed\.

Deleting database file 'D:\SQL Data\MSSQL\Data\wpi_koog_db2_undo.ldf'.
Processed 6 pages for database 'wpi_koog_db2', file 'wpi_koog_db2_Log' on file 1.
RESTORE LOG successfully processed 6 pages in 0.001 seconds (47.616 MB/sec).


9-6-2005 15:24:00: SQL Backup 3.1.0, (c) Red Gate Software Ltd 2004 - 2005
9-6-2005 15:24:00:
9-6-2005 15:24:00: Restoring transaction logs for database wpi_koog_db2 from "D:\SQL Data\tlogs_restore\*wpi_koog_db2*.sqb"
9-6-2005 15:24:00:
9-6-2005 15:24:00: Processing log group 0 ...
9-6-2005 15:24:00: \SQL Data\tlogs_restore\LOG_(local)_wpi_koog_db2_20050609 152402.sqb
9-6-2005 15:24:00: RESTORE LOG [wpi_koog_db2] FROM DISK = 'D:\SQL Data\tlogs_restore\LOG_(local)_wpi_koog_db2_20050609 152402.sqb' WITH STANDBY = 'D:\SQL Data\MSSQL\Data\wpi_koog_db2_undo.ldf', MOVETO = 'D:\SQL Data\tlogs_restore\processed\'
9-6-2005 15:24:00:
9-6-2005 15:24:00:
9-6-2005 15:24:02: Completed.
9-6-2005 15:24:02:
9-6-2005 15:24:02: Moved LOG_(local)_wpi_koog_db2_20050609 152402.sqb to \SQL Data\tlogs_restore\processed\.

Deleting database file 'D:\SQL Data\MSSQL\Data\wpi_koog_db2_undo.ldf'.
Processed 1 pages for database 'wpi_koog_db2', file 'wpi_koog_db2_Log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.001 seconds (3.072 MB/sec).


9-6-2005 15:25:00: SQL Backup 3.1.0, (c) Red Gate Software Ltd 2004 - 2005
9-6-2005 15:25:00:
9-6-2005 15:25:00: Restoring transaction logs for database wpi_koog_db2 from "D:\SQL Data\tlogs_restore\*wpi_koog_db2*.sqb"
9-6-2005 15:25:00:
9-6-2005 15:25:00: No log files found to be restored.

9-6-2005 15:26:00: SQL Backup 3.1.0, (c) Red Gate Software Ltd 2004 - 2005
9-6-2005 15:26:00:
9-6-2005 15:26:00: Restoring transaction logs for database wpi_koog_db2 from "D:\SQL Data\tlogs_restore\*wpi_koog_db2*.sqb"
9-6-2005 15:26:00:
9-6-2005 15:26:00: Processing log group 0 ...
9-6-2005 15:26:00: \SQL Data\tlogs_restore\LOG_(local)_wpi_koog_db2_20050609 152602.sqb
9-6-2005 15:26:00: RESTORE LOG [wpi_koog_db2] FROM DISK = 'D:\SQL Data\tlogs_restore\LOG_(local)_wpi_koog_db2_20050609 152602.sqb' WITH STANDBY = 'D:\SQL Data\MSSQL\Data\wpi_koog_db2_undo.ldf', MOVETO = 'D:\SQL Data\tlogs_restore\processed\'
9-6-2005 15:26:00:
9-6-2005 15:26:00:
9-6-2005 15:26:00: Completed.
9-6-2005 15:26:00:
9-6-2005 15:26:00: Moved LOG_(local)_wpi_koog_db2_20050609 152602.sqb to \SQL Data\tlogs_restore\processed\.

Msg 4305, Level 16, State 1, Server 728PRPRINS1, Line 1
The log in this backup set begins at LSN 453000000056300001, which is too late to apply to the database. An earlier log
backup that includes LSN 453000000055800001 can be restored.
Msg 3013, Level 16, State 1, Server 728PRPRINS1, Line 1
RESTORE LOG is terminating abnormally.

Daniel Handley

Hi Hans

If there are multiple log backup files in the restore directory SQL Backup will read information from all of them and restore them in the correct order. This means that the database will not be out of sync.

In your case the file was not copied across so if a file is missing the standby server will be out of sync. There is no way to prevent this included in the software as the restore will use any files it finds.

What should be established is why there was no file. If the backup didn't take place then all should be ok as the next file to be copied across will continue from the last.

A potential work around is to add a count to the file name so that the server are in sync. If the count number is out then the files would not be restored.


As a note when setting up log shipping i would recommend having a full backup copied across each night. This will ensure that the databases are in sync each day, and reduce the amount of backup files needed for a restore. To automate this you can use the scripts in other posts but change the date field to:
@datestamp = CAST(DATEPART(m, getdate())AS varchar(5))+ CAST(DATEPART(d, getdate())AS varchar(5))+ CAST(DATEPART(yy, getdate())AS varchar(5))
This will remove the hours minutes and second so you know the filename for the restore process.

Regards
Dan