Log shipping issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Log shipping issue

Author

Topic

bigbelly
Starting Member

39 Posts

Posted - 2006-05-24 : 03:27:44

I am trying to implement log shipping. For this purpose i did a complete backup of the databas from the production server and restored it on the stand-by server. Now for restoring the transaction log i am running a job that runs after every 1 hr . The steps in the jobs are as below:
Step 1: Backup Log
BACKUP LOG PrePaidCard TO PrePaidCard_log_backup_device WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'

Step 2: Copy Log
xcopy d:\SQL2000\MSSQL\BACKUP\PrePaidCard_log_backup_device.BAK \\LEONING\d$\PrePaidCard-TransationBackUp\ /c

Step 3: Restore Log
EXEC LEONING.master.dbo.restore_PrePaidCard_log_backups

Now because the path was not correct in step 2 the job failed for the first instance. So i corrected the path and ran the job again.
But it failed in step 3 with the following error:

"RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013) The log in this backup set begins at LSN 383150000002099800001, which is too late to apply to the database. An earlier log backup that includes LSN 383149000019189400001 can be restored."

Now ,if i do a backup of the database from Production server agian and restore it on backup server and then run the job for transaction log it works fine.

So ,is it that since the job failed initially i always need to do a complete backup of the database again and then restore it on the stand by server and then execute the job set for transaction log?
OR is there some other ways with which successful restore of transaction log could be achived?

mr_mist
Grunnio

1870 Posts

Posted - 2006-05-24 : 03:50:02

You will need to start again from a complete backup if you have somehow broken the transaction log chain. The error message means that a log backup file is missing in between the full backup and the log you are trying to apply.

-------
Moo. :)

Kristen
Test

22859 Posts

Posted - 2006-05-24 : 05:05:40

I suggest you rename the LOG Backups on a round-robin so that you have the last half dozen or so - e.g. this would retain the last 10 (without the complication of including date/time in the filename and having to manage deletion-by-date etc):

xcopy d:\SQL2000\MSSQL\BACKUP\PrePaidCard_log_backup_device.BAK \\LEONING\d$\PrePaidCard-TransationBackUp\ /c

DEL d:\SQL2000\MSSQL\BACKUP\PrePaidCard_log_backup_device_10.BAK
REN d:\SQL2000\MSSQL\BACKUP\PrePaidCard_log_backup_device_09.BAK PrePaidCard_log_backup_device_10.BAK
REN d:\SQL2000\MSSQL\BACKUP\PrePaidCard_log_backup_device_08.BAK PrePaidCard_log_backup_device_09.BAK
...
REN d:\SQL2000\MSSQL\BACKUP\PrePaidCard_log_backup_device_01.BAK PrePaidCard_log_backup_device_02.BAK
REN d:\SQL2000\MSSQL\BACKUP\PrePaidCard_log_backup_device.BAK PrePaidCard_log_backup_device_01.BAK

Then if something goes wrong (disk full on target server, network outage, etc) you've got the last few backups to manually retrieve the situation. Makes sense to keep enough for you to discover there was a problem and rectify it - e.g. Friday 4pm to Monday 10am is about 65 backups - maybe keep the last 100 for good measure!!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources