Restore Tlog on SQL 6.5

Hello everyone,
I am trying to apply transaction log dumps to a database and it is
giving me the error:
Specified file 'X:\mssql\BACKUP\PrimoProd_tlog_amdump.DAT' is out of
sequence. Current time stamp is May 20 2004 8:31PM while dump was from
May 23 2004 7:41PM.
Here are the details: I restored the database with the full backup from
05/20/2004 9:00 pm. Now I am trying to apply the Tlogs that were backed
up starting 05/21/04 at 6:00 am. The Tlogs were backed up to a single
device. That is, the backup file primoprod_tlog_dump.dat has all the
tlog backups on it from the 05/21 am to about 05/23 pm. Therefore the
date stamp on that file is 05/23. This is what SQL 6.5 is complaining
about. I restored the database using a file that was backed up on 05/20
and now I am applying logs from a file which has a date stamp of 05/23
but this file does have the tlogs from 05/21 within it. What should I
do? I am specifying the correct tolog to apply i.e. from 05/21 am. The
very first tlog after the full backup.
Raziq.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Hi,
I can see 2 issues with your restore.
First issue.
--
The destination database you are trying to restore shoule be set to below
options
1. Read Only
2. no chkpt on recovery
How to set this options
sp_dboption dbname,'Read Only',True
go
sp_dboption dbname,'no chkpt on recovery',True
Second Issue
--
As you say "The Tlogs were backed up to a single device." - Did you mean all
the transaction logs are
appended to the same backup file". Then you cant load the transaction
directly.
Firt execute the below statement to get the Sequence details:-
load headeronly from disk='e:\mssql\backup\backup_file_name_w
ith_extension'
(Change the directory based on yours)
The above command will give you the sequence details for each transaction
log backup
available inside the backup file
Some thing like:
Dumptype Database Striped Compressed Sequence
-- -- -- -- --
2 vanrpt 0 0 1
2 vanrpt 0 0 2
So while loading the transaction log backup in destination database you have
to specify FILE = @.filenumber
(File number is nothing but the sequence number provided by LOAD Headeronly
command)
How to load the transaction log
Load transaction dbname from disk='backupfile' with file=1
go
Load transaction dbname from disk='backupfile' with file=2
Note:
Before doing the Load set the database options mentioned in "First Issue"
Thanks
Hari
MCDBA
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:O40suVbQEHA.964@.TK2MSFTNGP10.phx.gbl...
> Hello everyone,
> I am trying to apply transaction log dumps to a database and it is
> giving me the error:
> Specified file 'X:\mssql\BACKUP\PrimoProd_tlog_amdump.DAT' is out of
> sequence. Current time stamp is May 20 2004 8:31PM while dump was from
> May 23 2004 7:41PM.
> Here are the details: I restored the database with the full backup from
> 05/20/2004 9:00 pm. Now I am trying to apply the Tlogs that were backed
> up starting 05/21/04 at 6:00 am. The Tlogs were backed up to a single
> device. That is, the backup file primoprod_tlog_dump.dat has all the
> tlog backups on it from the 05/21 am to about 05/23 pm. Therefore the
> date stamp on that file is 05/23. This is what SQL 6.5 is complaining
> about. I restored the database using a file that was backed up on 05/20
> and now I am applying logs from a file which has a date stamp of 05/23
> but this file does have the tlogs from 05/21 within it. What should I
> do? I am specifying the correct tolog to apply i.e. from 05/21 am. The
> very first tlog after the full backup.
> Raziq.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks for your reply Hari, I have one other question. Is it possible
to indicate in the restore tlog script to chose the very last backup in
the backup device to restore. I.e. if your backup device has four
backups in it can you indicate on your script to choose the very last
one (in this case it would be 4) without indicating the number 4.
Thanks,
Raziq.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!