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_with_extens ion'
(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!
sql
Showing posts with label tlog. Show all posts
Showing posts with label tlog. Show all posts
Monday, March 26, 2012
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.developersdex.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_with_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.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
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.developersdex.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_with_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.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
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!
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!
Tuesday, March 20, 2012
Restore Question
Hello,
I'm testing restoring a db with 5 tlogs using the norecovery option until
the last tlog. During testing I happened to restore the logs out of order,
but did not receive an error message during the out of order log restore. Is
this normal? I would think restoring logs out of sequence would generate an
error.
Any help appreciated!
Thanks in advance.Yes, it should generate an error. Perhaps you did zero modifications between the log backups? In
that pretty special case, there would be now log records to roll forward, and I believe that SQL
Server would because of that not complain about out-of-sequence (since there isn't anything to
sequence over).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:D82E2BC4-BF82-4E46-8D01-9E4B8AD92220@.microsoft.com...
> Hello,
> I'm testing restoring a db with 5 tlogs using the norecovery option until
> the last tlog. During testing I happened to restore the logs out of order,
> but did not receive an error message during the out of order log restore. Is
> this normal? I would think restoring logs out of sequence would generate an
> error.
> Any help appreciated!
> Thanks in advance.
I'm testing restoring a db with 5 tlogs using the norecovery option until
the last tlog. During testing I happened to restore the logs out of order,
but did not receive an error message during the out of order log restore. Is
this normal? I would think restoring logs out of sequence would generate an
error.
Any help appreciated!
Thanks in advance.Yes, it should generate an error. Perhaps you did zero modifications between the log backups? In
that pretty special case, there would be now log records to roll forward, and I believe that SQL
Server would because of that not complain about out-of-sequence (since there isn't anything to
sequence over).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:D82E2BC4-BF82-4E46-8D01-9E4B8AD92220@.microsoft.com...
> Hello,
> I'm testing restoring a db with 5 tlogs using the norecovery option until
> the last tlog. During testing I happened to restore the logs out of order,
> but did not receive an error message during the out of order log restore. Is
> this normal? I would think restoring logs out of sequence would generate an
> error.
> Any help appreciated!
> Thanks in advance.
Monday, March 12, 2012
Restore Problem
Hi,
I am trying to restore a point in time restore of backup. My bkp plan includes full bkp-weekly, daily diff bkp and every 2 hr TLog bkp. I would like to restore the latest bkp as on ''2007-10-18 ' with minimul data loss. Hence i follow the following seqence of restores;
--Restore Full backup
RESTORE DATABASE [ABC] FROM ABC_BackUp WITH FILE = 1, NORECOVERY
GO
--Restore Diff backup
RESTORE DATABASE ABC FROM ABC_BackUp WITH FILE = 3
--Restore TLog backup
RESTORE LOG ABC FROM ABC_BackUp WITH FILE=4, NORECOVERY, STOPAT = '2007-10-18 15:11:00'
GO
The system works well with Full bkp and Diff bkp restore but throws following error while TLog restore;
ERROR:
Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 86001000000103200001, which is too late to apply to the database. An earlier log backup that includes LSN 86001000000081300001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
The following is the backup information of my database:-
Pls. guide why i get this error. I don't do any shrinking between Full bkp and TLog bkps. Thanks in advance.
Please mention the timings for Full, Differential and Transaction log backups from the above sequence, it seems the error is by default as the LSN is mismatched.|||Full backp : every sunday 7.00 am
Diff backup: daily 11.00 pm
trn backup: every 2 hrs mon - sat
|||hello,
i just wanted to say that i have the same promblem when trying to restore poin-in-time on a different server... maybe this helps.
i can do point-in-time restores on the same server.
i believe the log sequences get mixed up on other servers.
is there a workaround?
thank you
Hugo
|||As explained it refers on the LSN matchup, also mention the restore sequence as it seems the backup database & log are not matched up when restored.|||i was wrong... i was backing up the log to two different files... sorry. everything is working now.
Subscribe to:
Posts (Atom)