Re: POINT IN TIME RESTORE

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 03/23/04 

Date: Tue, 23 Mar 2004 16:03:03 +0100

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>...
> > 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

Relevant Pages

  • Backup and Restore Files
    ... What is the best way to backup and restore a runtime database file? ... pszDisplayName As String ...
    (microsoft.public.access.gettingstarted)
  • RE: How do I restore from mdf and ldf files?
    ... Tasks -> Backup Database ... Tasks -> Restore Database ... Windows 2003 Server with Latest Service Pack ... Pre-requisites for Sharepoint Backup and Restore: ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Restoring SQL Server Backups
    ... ORIGINAL BACKUP & RESTORE (MSDE Database) ... occurrenceof cachestore flush for the 'Object Plans' cachestore (part ...
    (microsoft.public.sqlserver.server)
  • Re: Restoring SQL Server Backups
    ... ORIGINAL BACKUP & RESTORE (MSDE Database) ... occurrenceof cachestore flush for the 'Object Plans' cachestore (part ...
    (microsoft.public.sqlserver.server)
  • Re: Backend Exchange migrate to New Hardware (Server)
    ... Exchange mailbox stores and Exchange public folder stores. ... When you use Backup to restore Exchange databases, ... Storage Engine to restore Exchange database files and their ...
    (microsoft.public.exchange.setup)