Log shipping in SQL Server 2000

Question

Log shipping in SQL Server 2000

Robert Broyles

SSC Journeyman

Points: 91
More actions
October 11, 2005 at 6:58 am

#169284

I keep getting error messages when I try to run a stored procedure on the standby server. I am currently running the database backup up once a day and then running transaction log backups every 20 minutes. The full database backup runs fine with no problems. the transaction log process always gets hung up with either of the two following errors.
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step. [SQLSTATE 42000] (Error 4306). The step failed.
or
the transaction log is to late to apply try using an earlier transaction log beginning with a transaction log number.( this is obviously paraphrased. I know why I get this message, but not the above error.
I am new to this and I am still trying to get a handle on this.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 1

Do you restore the full backup into the standby server? if yes, do you restore "with norecovery" or "with standby=xxx.ldf"?

Do you restore all transaction log backups also "with norecovery" or "with standby=xxx.ldf"?

Is each restore finished before the next backup/restore cycle starts?

karl

Best regards
karl

Robert Broyles SSC Journeyman Points: 91 More actions · Answer 2

I may have made some mistakes on where I put statements, but this is what I have on my servers. If you see anything that is out of place let me know. I can manually do a Restore on the standby server with Standby and then The transaction log will run fine all day without a problem until the Database restore is done at midnight.

Thank you for your help

Step 1

BACKUP LOG Database_Name

WITH TRUNCATE_ONLY

WAITFOR DELAY '00:00:05'

Step 2

BACKUP DATABASE [ARSystem] TO [ARSYSTEM_BACKUP_DEVICE] WITH NOINIT , NOUNLOAD , NAME = N'ARSystem backup', NOSKIP , STATS = 10, NOFORMAT

Step 3

c:\xcopyarsystem.bat

which does this:

XCOPY D:\MSSQL\BACKUP\ARSystem\ARSYSTEM_BACKUP_DEVICE.BAK \\Backup_Server_Name\D$\MSSQL\BACKUP\ARSystem\ /y

Step 4

exec eglinitcfo.master.dbo.sp_restore_database_backups

which does this:

CREATE PROCEDURE [dbo].[sp_restore_database_backups]

AS

RESTORE DATABASE ARSystem

FROM DISK = 'D:\MSSQL\BACKUP\ARSYSTEM\ARSYSTEM_BACKUP_DEVICE.BAK'

WITH

REPLACE,

STANDBY = 'D:\MSSQL\BACKUP\undo_ARSystem.ldf',

MOVE 'ARSYSTEM' TO 'D:\MSSQL\Data\ARSystem.mdf',

MOVE 'ARSYSTEM_LOG' TO 'D:\MSSQL\Data\ARSystem_log.ldf'

WAITFOR DELAY '00:00:05'

EXEC SP_DBOPTION 'ARSystem','SINGLE USER',TRUE

GO

The Next job is for the transaction log.

Step 1

BACKUP LOG [ARSystem] TO [ARSYSTEM_LOG_BACKUP_DEVICE] WITH INIT , NOUNLOAD , NAME = N'ARSystem log backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE

Step 2

c:\xcopyarsystemlog.bat

which does this:

XCOPY D:\MSSQL\BACKUP\ARSystem\ARSYSTEM_LOG_BACKUP_DEVICE.BAK \\Backup_Server_Name\D$\MSSQL\BACKUP\ARSystem /y

Step 3

This is the stored procedure that is in the Master Database Stored Procedures

EXEC eglinitcfo.master.dbo.sp_restore_log_backups

which does this:

CREATE PROCEDURE [dbo].[sp_restore_log_backups] AS

RESTORE LOG ARSYSTEM

FROM DISK = 'D:\MSSQL\BACKUP\ARSystem\ARSYSTEM_LOG_BACKUP_DEVICE.BAK'

WITH

STANDBY = 'D:\MSSQL\BACKUP\undo_ARSystem.ldf'

WAITFOR DELAY '00:00:05'

EXEC SP_DBOPTION 'ARSystem','SINGLE USER', TRUE

GO

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 3

In Step 1 you should do an

BACKUP LOG Database_Name WITH TRUNCATE

instead of an

BACKUP LOG Database_Name WITH TRUNCATE_ONLY,

because with truncate_only you are cutting the chain of log backups, and you can not restore up to date from an backup that is older than one day.

The rest of your procedure seems ok to me.

Just one more point:

Make sure that there is enough time between full restore and the first log restore. If you try to restore the first log backup before the full restore is finished, that log restore will not even start, and the next log restore needs the first for the LSN-chain.

To be able to restore even then you would have to look for the last LSN in the standby-db, end then look for the backup file containing the next LSN, and restore that one first. That is a bit complicated, but possible...

karl

Best regards
karl

Robert Broyles SSC Journeyman Points: 91 More actions · Answer 4

Thank you for you help. I will try your suggestion. this has been bothering me for two weeks now.

Rob Broyles

philcart SSC-Forever Points: 47796 More actions · Answer 5

Couple of points,

You shouldn't really be placing your own stored procedures in the master database. Who knows what could happen with the next service pack, etc...

Better off creating your own "Utility" database and putting the stored procedures there. You would call them in the same manner that you're doing now anyway.

Also, you might want to consider using ROBOCOPY from the Windows Resource Kit. It's faster and more reliable than XCOPY.

--------------------
Colt 45 - the original point and click interface

Robert Broyles SSC Journeyman Points: 91 More actions · Answer 6

I put the stored procedures in the master database, because that is how Log shipping steps were explained to me. You are the first person that has told me to create a utility database.

I have been told to use robocopy, but I am trying to check out all the parameters of ROBOCOPY before I use it.

Thank you

Rob Broyles