Re: Restore Tlog on SQL 6.5

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 05/25/04 

Date: Tue, 25 May 2004 09:24:54 +0530

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!

Relevant Pages

  • Re: Restoring from non-truncated transaction log
    ... The transaction log is there to support transactional consistency as well ... as supporting "incremental" backup. ... A database backup contains both data pages as well as the necessary log records (from the ... When your restore a database backup, ...
    (microsoft.public.sqlserver.server)
  • RE: Recovering Documents
    ... the blob. ... This is the only database I have that uses a blob field so I'me not ... If you're trying to get where you can restore just part of a document your ... > are wondering if transaction log will capture the information and be of use ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Restoring from non-truncated transaction log
    ... > That restore overwrites everything inside the database. ... This of a restore like applying a VM Ware image on a machine. ... When the restore was done it wasn't restoring the transaction log. ... > Are you saying that you saved something, somehow (backup, detach) before that restore was performed? ...
    (microsoft.public.sqlserver.server)
  • Re: POINT IN TIME RESTORE
    ... Perform a transaction log backup of the original database (If you have ... Restore the subsequent transaction log files in order of backup WITH ...
    (microsoft.public.sqlserver.server)
  • Re: Restore Sql6.5 dump to different server
    ... > It's a long time since I restored a 6.5 database! ... > devices and a database to load into? ... >> after selecting the file i want to restore from. ...
    (microsoft.public.sqlserver.setup)