problem with my drp test job - 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)

problem with my drp test job

Author

Topic

joanne
Starting Member

46 Posts

Posted - 2005-11-15 : 14:27:09

Message Preview
Hi,
I want to develop a recovery strategy for my product server by using a warm standby server (I use standard edition) with log shipping method.
To maintain this, I created some jobs on product and drp server like that:
Product server
Full_backup_DBMMI (scheduled daily at 22:30 )
1. clear down log: backup log DBMMI with no_log
2. local full backup
BACKUP DATABASE @DBName
TO DISK = @filename
WITH INIT
3. ftp transfer to standby server
4. execute remote job (full_restore_DBMMI)
restore database @DBName
from disk = @filename
with
REPLACE,
STANDBY =@filename1

Log_backup_DBMMI (every 6 hours, between 9 AM and 10 PM)

1. local backup log
BACKUP log @DBName
TO DISK = @filename WITH INIT, NO_TRUNCATE
2. ftp transfer to standby server
3. run remote job to restore the log:
restore log @DBName
from disk = @filename
WITH STANDBY=@ filename1

Now, all the jobs are in place and I want to test everything to be sure the jobs and stored procedures work properly.

These are the steps to follow when failing over my production server to my standby server.
Drp_job

1. Backup log with no_truncate
2. ftp transfer
3. remote job to restore the last log, with 2 steps:

3.1 restore log @DBName
from disk = @filename
WITH STANDBY=@ filename1
3.2 RESTORE DATABASE DBMMI WITH RECOVERY
EXEC SP_DBOPTION 'DBMMI', 'read only', 'false'
EXEC SP_DBOPTION 'DBMMI', 'dbo use only', 'false'

My test:
Full backup 8:30
Log_backup 9:30
Log_backup 10:30

Drp_log 11:30

Executed as user: sa. The log in this backup set begins at LSN 18308000000053600001, which is too late to apply to the database. An earlier log backup that includes LSN 18295000000008900001 can be restored. [SQLSTATE 42000] (Error 4305) RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

What is wrong in my procedure?

Many thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-15 : 17:01:16

Your problem is here:
1. clear down log: backup log DBMMI with no_log

You can't do that as it invalidates the tlog chain. You have to start with a full backup and the entire tlog chain.

Tara Kizer
aka tduggan

joanne
Starting Member

46 Posts

Posted - 2005-11-16 : 19:27:28

Hi Tara,
Thanks very much for your answer.All work fine now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources