POINT IN TIME RESTORE

When I try to restore a database with query analyser and point in time resto
re, I always obtain this error message : The log in this backup set terminat
es at LSN 69508000000440000001, which is too early to apply to the database.
A more recent log backup t
hat includes LSN 69508000000440300001 can be restored.
My request is : RESTORE DATABASE TEST1 FROM BBA with norecovery
RESTORE LOG TEST1 FROM BBA
WITH recovery, STOPAT = '2004-03-07 8:30:00'
I have the same problem with all my databases and if I look in my device BBA
, I can see that I start with a full backup on 2004-03-07 and I continue wit
h logs backup until 2004-03-13.
What's wrong ?I forgot to mention that my full backup were at 5:00 AM on 2004-03-07|||Is there only 1 tlog created upto "8:30" and after?
If there are multiple tlogs you'll have to issue something like...
restore database dbname.bak from bba with norecovery
restore log logname1.tlog from bba with norecovery
restore log logname2.tlog from bba with recovery, stopat = '2004-03-07 8:30:
00'
Regards,
Craig.
Cris wrote:

> When I try to restore a database with query analyser and point in time restore, I
always obtain this error message : The log in this backup set terminates at LSN 6950
8000000440000001, which is too early to apply to the database. A more recent log bac
kup
that includes LSN 69508000000440300001 can be restored.
> My request is : RESTORE DATABASE TEST1 FROM BBA with norecovery
> RESTORE LOG TEST1 FROM BBA
> WITH recovery, STOPAT = '2004-03-07 8:30:00'
> I have the same problem with all my databases and if I look in my device B
BA, I can see that I start with a full backup on 2004-03-07 and I continue w
ith logs backup until 2004-03-13.
> What's wrong ?|||Hi,
To add on to craigs posting,
The POINT-IN-TIME recovery will work only if the database's Recovery moel is
set to "FULL" and the model is not
changed after theFULL database backup.
Steps:
1. Perform a transaction log backup of the original database (If you have
not performed one)
2. RESTORE DATABASE TEST1 FROM BBA (Give the correct backup file / device
name) WITH NORECOVERY
3. Restore the subsequent transaction log files in order of backup WITH
NORECOVERY option till the final transaction log file
4. In the the final transaction log restore mention WITH RECOVERY and
STOPAT='date and time'
Thanks
Hari
MCDBA
"Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
news:#T$bgUICEHA.2556@.TK2MSFTNGP12.phx.gbl...
> Is there only 1 tlog created upto "8:30" and after?
> If there are multiple tlogs you'll have to issue something like...
> restore database dbname.bak from bba with norecovery
> restore log logname1.tlog from bba with norecovery
> restore log logname2.tlog from bba with recovery, stopat = '2004-03-07
8:30:00'
> Regards,
> Craig.
> Cris wrote:
>
restore, I always obtain this error message : The log in this backup set
terminates at LSN 69508000000440000001, which is too early to apply to the
database. A more recent log backup that includes LSN 69508000000440300001
can be restored.
BBA, I can see that I start with a full backup on 2004-03-07 and I continue
with logs backup until 2004-03-13.|||Sorry to jump in, I have read the thread and it is close to what I
would like to accomplish but not quite.
I have a perfefctly good database with a .mdf and a .ldf file. The
recovery model is FULL. It was a new database a few weeks ago for
developing a new product.
We entered all kinds of info into it before we realized we were not
backing it up. Today we erased all the data...oops..
So I have a good .mdf and a full .ldf, I do a backup of both (wrong
thing)? And I try to restore using the cdoe I have seen here before..
RESTORE DATABASE mydatabase FROM DISK='c:\mybackup\pnedata' WITH
NORECOVERY;
RESTORE LOG PNEBilling FROM DISK='c:\mybackup\pnedata' WITH RECOVERY,
STOPAT = 'Mar 18, 2004 10:00 AM';
This runs without error but restores the database to its CURRENT state
which is empty of data!
If I run the same from Enterprise Manager and I check the "Point in
time" it will not allow me to select anything but today.
It seems that I should be able to do this since I have a full .ldf
file but the method is beyond my technical skills.
Any ideas?
Thanks
Tim|||You haven't provided us with exact information of what you have, and at
which point in time those backups (loosely speaking) were taken.

> We entered all kinds of info into it before we realized we were not
> backing it up. Today we erased all the data...oops..
OK, so what we need to know is, compared to this point in time (when you
erased all data), what file backups (ldf, mdf) and database backups (BACKUP
DATABASE) and transaction log backups (BACKUP LOG) you have. Again, put it
on a time line compared to the erase of the data.

> So I have a good .mdf and a full .ldf, I do a backup of both (wrong
> thing)?
You generally don't do backup of the underlying database files in SQL
Server. Use BACKUP DATABASE and BACKUP LOG instead.
Then you continue to say that you use the RESTORE command, but you haven't
given us the information about when you did the BACKUP DATABASE and BACKUP
LOG.
You are probably toast, though. My guess is that you have some old file
level backup. And after the disaster, you did BACKUP DATABASE and BACKUP
LOG. You then put back those old database files, but want to come closer to
the point of disaster. So you do the RESTORE DATABASE. And we don't need to
go no further, because that put the database in the same state as it was
when the backup command was taken (which was after the point of disaster).
You really need to read the sections about backup and restore in Books
Online to understand how these things work in SQL Server. If you want more
help with this particular situation, please provide more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tim" <tsullivan@.websul.net> wrote in message
news:a1920b75.0403221607.6e054dc9@.posting.google.com...
> Sorry to jump in, I have read the thread and it is close to what I
> would like to accomplish but not quite.
> I have a perfefctly good database with a .mdf and a .ldf file. The
> recovery model is FULL. It was a new database a few weeks ago for
> developing a new product.
> We entered all kinds of info into it before we realized we were not
> backing it up. Today we erased all the data...oops..
> So I have a good .mdf and a full .ldf, I do a backup of both (wrong
> thing)? And I try to restore using the cdoe I have seen here before..
> RESTORE DATABASE mydatabase FROM DISK='c:\mybackup\pnedata' WITH
> NORECOVERY;
> RESTORE LOG PNEBilling FROM DISK='c:\mybackup\pnedata' WITH RECOVERY,
> STOPAT = 'Mar 18, 2004 10:00 AM';
> This runs without error but restores the database to its CURRENT state
> which is empty of data!
> If I run the same from Enterprise Manager and I check the "Point in
> time" it will not allow me to select anything but today.
> It seems that I should be able to do this since I have a full .ldf
> file but the method is beyond my technical skills.
>
> Any ideas?
> Thanks
> Tim|||Tibor,
Thanks for trying to help a dummy
You are right on many fronts
a) I need more knowledge in BOL
b) I didn't decribe clearly, due to "a"
Here is the timeline
1- create database
2- enter data
3- erase data
4 - enterprise manager backup database, backup transaction log
5- restore database with norecovery
6- restore log with recovery, stopat (date time before data erased)
I use osql to run the job, the job runs but all the data is still gone
If I run the same restore from enterprise manager I am unable to
select a date prior to the time of the 1st transaction log backup.
I guess that is the point, you have to have a database backup from
BEFORE the point in time you want to restore to...
I am toast eh?
Tim
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<#
6Mb5LLEEHA.3412@.TK2MSFTNGP10.phx.gbl>...
> You haven't provided us with exact information of what you have, and at
> which point in time those backups (loosely speaking) were taken.
>
> OK, so what we need to know is, compared to this point in time (when you
> erased all data), what file backups (ldf, mdf) and database backups (BACKU
P
> DATABASE) and transaction log backups (BACKUP LOG) you have. Again, put it
> on a time line compared to the erase of the data.
>
> You generally don't do backup of the underlying database files in SQL
> Server. Use BACKUP DATABASE and BACKUP LOG instead.
> Then you continue to say that you use the RESTORE command, but you haven't
> given us the information about when you did the BACKUP DATABASE and BACKUP
> LOG.
> You are probably toast, though. My guess is that you have some old file
> level backup. And after the disaster, you did BACKUP DATABASE and BACKUP
> LOG. You then put back those old database files, but want to come closer t
o
> the point of disaster. So you do the RESTORE DATABASE. And we don't need t
o
> go no further, because that put the database in the same state as it was
> when the backup command was taken (which was after the point of disaster).
> You really need to read the sections about backup and restore in Books
> Online to understand how these things work in SQL Server. If you want more
> help with this particular situation, please provide more information.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Tim" <tsullivan@.websul.net> wrote in message
> news:a1920b75.0403221607.6e054dc9@.posting.google.com...|||This is what you should have done in order to do the restore as you wish:

> 1- create database
> 2- enter data
BACKUP DATABASE
> 3- erase data
BACKUP LOG
> 5- restore database with norecovery
> 6- restore log with recovery, stopat (date time before data erased)
The BACKUP DATABAE can of course be at an earlier point in time, but not
later, I'm afraid.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tim" <tsullivan@.websul.net> wrote in message
news:a1920b75.0403230651.23d72ffe@.posting.google.com...
> Tibor,
> Thanks for trying to help a dummy
> You are right on many fronts
> a) I need more knowledge in BOL
> b) I didn't decribe clearly, due to "a"
> Here is the timeline
> 1- create database
> 2- enter data
> 3- erase data
> 4 - enterprise manager backup database, backup transaction log
> 5- restore database with norecovery
> 6- restore log with recovery, stopat (date time before data erased)
> I use osql to run the job, the job runs but all the data is still gone
> If I run the same restore from enterprise manager I am unable to
> select a date prior to the time of the 1st transaction log backup.
> I guess that is the point, you have to have a database backup from
> BEFORE the point in time you want to restore to...
> I am toast eh?
> Tim
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message news:<#6Mb5LLEEHA.3412@.TK2MSFTNGP10.phx.gbl>...
(BACKUP
it
haven't
BACKUP
to
to
disaster).
more|||Try like this
[url]http://www.sql-server-performance.com/lockwoodtech_log_navigator_spotlight.asp[/ur
l]

> Sorry to jump in, I have read the thread and it is close to what I
> would like to accomplish but not quite.
> I have a perfefctly good database with a .mdf and a .ldf file. The
> recovery model is FULL. It was a new database a few weeks ago for
> developing a new product.
> We entered all kinds of info into it before we realized we were not
> backing it up. Today we erased all the data...oops..
> So I have a good .mdf and a full .ldf, I do a backup of both (wrong
> thing)? And I try to restore using the cdoe I have seen here before..
> RESTORE DATABASE mydatabase FROM DISK='c:\mybackup\pnedata' WITH
> NORECOVERY;
> RESTORE LOG PNEBilling FROM DISK='c:\mybackup\pnedata' WITH RECOVERY,
> STOPAT = 'Mar 18, 2004 10:00 AM';
> This runs without error but restores the database to its CURRENT state
> which is empty of data!
> If I run the same from Enterprise Manager and I check the "Point in
> time" it will not allow me to select anything but today.
> It seems that I should be able to do this since I have a full .ldf
> file but the method is beyond my technical skills.
>
> Any ideas?
> Thanks
> Timsql