Partial Restore from Log  
Author Message
Douggie





PostPosted: Tue May 18 15:50:53 CDT 2004 Top

SQL Server >> Partial Restore from Log

We have a database for our Beta site whose backups were not working successfully. A developer inadvertently dropped and recreated a
table with important data in it today, and so we are stuck without a backup.

We would like to restore the database to the state it was in this morning, and hoped to be able to do it from the transaction log.
The problem is, I need to apply the log only up until the point that the DROP TABLE was issued as I don't want to lose the data
again.

All the documentation seems to indicate that what we want to do is possible, but I can't find how to do specifically what we want to
do. Can someone point me in the right direction?

SQL Server291  
 
 
Gregory





PostPosted: Tue May 18 15:50:53 CDT 2004 Top

SQL Server >> Partial Restore from Log Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Online.
The STOPAT parameter allows you to identify the point in time where you
would like to restore your database to. The time you would want to use is
the point just before your table got deleted.

--

----------------------------------------------------------------------------
----------------------------------------------------------------------------
----

Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples


> We have a database for our Beta site whose backups were not working
successfully. A developer inadvertently dropped and recreated a
> table with important data in it today, and so we are stuck without a
backup.
>
> We would like to restore the database to the state it was in this morning,
and hoped to be able to do it from the transaction log.
> The problem is, I need to apply the log only up until the point that the
DROP TABLE was issued as I don't want to lose the data
> again.
>
> All the documentation seems to indicate that what we want to do is
possible, but I can't find how to do specifically what we want to
> do. Can someone point me in the right direction?
>
>
>
>


 
 
Erik





PostPosted: Tue May 18 16:42:43 CDT 2004 Top

SQL Server >> Partial Restore from Log > Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Online.

Thanks- I was just trying to get STOPAT to work, but I seem to be missing a basic concept or two about the RESTORE LOG command.

I created a copy of my real database by scripting out all the tables and running the script. I figured I would restore the copy of
the database so I could leave all the other data in the production database in tact, and after the restore, I could just copy over
the data from the lost table. I tried:

RESTORE LOG CP2
FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'

Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log from the real database. (I have taken CPO offline
temporarily). But when I run the code above, I get the following message:

"Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH
NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally."

All the examples I can find seem to assume that I have a database backup that I am restoring from first, and _then_ I will apply the
log.



 
 
Gregory





PostPosted: Tue May 18 16:53:46 CDT 2004 Top

SQL Server >> Partial Restore from Log The basic concept of getting this to work, is making sure you have a
complete sequence of backups. Meaning you need at a mimimum a full database
backup and a transaction log backup where the table was deleted (provided no
other backups occured between the full and the transaction log backup) If
there were a number of transaction logs since the last full backup you need
all these backups. Also if you performed differential backups then based on
which files you are using for the restore you might need one of those. Do
you have all these backups, a complete sequence of backups from the full to
that last transaction log backup? If you don't have a complete set of
backups you will not be able to perform what I suggested.

Also the transaction log backup is not the actual transaction log (file
ending in .LDF), but separate file that is created when you issue a BACKUP
LOG command. Based on your restore command looks like you are trying to use
the actual transcation log for your "FROM DISK=" option.



--

----------------------------------------------------------------------------
----------------------------------------------------------------------------
----

Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples


> > Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books
Online.
>
> Thanks- I was just trying to get STOPAT to work, but I seem to be missing
a basic concept or two about the RESTORE LOG command.
>
> I created a copy of my real database by scripting out all the tables and
running the script. I figured I would restore the copy of
> the database so I could leave all the other data in the production
database in tact, and after the restore, I could just copy over
> the data from the lost table. I tried:
>
> RESTORE LOG CP2
> FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
> WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'
>
> Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log
from the real database. (I have taken CPO offline
> temporarily). But when I run the code above, I get the following message:
>
> "Server: Msg 4306, Level 16, State 1, Line 1
> The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH
> NORECOVERY or WITH STANDBY for all but the final step.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally."
>
> All the examples I can find seem to assume that I have a database backup
that I am restoring from first, and _then_ I will apply the
> log.
>
>
>


 
 
Erik





PostPosted: Tue May 18 17:08:40 CDT 2004 Top

SQL Server >> Partial Restore from Log > The basic concept of getting this to work, is making sure you have a
> complete sequence of backups.

Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the log was not backup up
at all, it should contain all the transactions since the birth of the database. (The log file is some 5GB in size).

I was hoping to accomplish this by starting with an empty database (the way it was when the log was started), and just applying the
complete log.

I'm getting a sinking feeling that this may not be possible?





 
 
Gregory





PostPosted: Tue May 18 17:17:06 CDT 2004 Top

SQL Server >> Partial Restore from Log If you don't have a full backup of you database then you are correct and you
can't restore from the transaction log. One glimmer of hope might be to try
and use a third party tools to get information out of the transaction log.
I've never used these. There is a tool called "Log Explorer" from Lumigent
which might help you out.
--

----------------------------------------------------------------------------
----------------------------------------------------------------------------
----

Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples


> > The basic concept of getting this to work, is making sure you have a
> > complete sequence of backups.
>
> Well the problem is, there are _no_ backups of the database or of the log.
But as I understand it, since the log was not backup up
> at all, it should contain all the transactions since the birth of the
database. (The log file is some 5GB in size).
>
> I was hoping to accomplish this by starting with an empty database (the
way it was when the log was started), and just applying the
> complete log.
>
> I'm getting a sinking feeling that this may not be possible?
>
>
>
>
>


 
 
Erik





PostPosted: Tue May 18 18:02:13 CDT 2004 Top

SQL Server >> Partial Restore from Log Thanks, I really appreciate your help. We're looking at alternative means of restoring the data from a sencondary source.



> If you don't have a full backup of you database then you are correct and you
> can't restore from the transaction log. One glimmer of hope might be to try
> and use a third party tools to get information out of the transaction log.
> I've never used these. There is a tool called "Log Explorer" from Lumigent
> which might help you out.
> --
>
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ----
>
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples


> > > The basic concept of getting this to work, is making sure you have a
> > > complete sequence of backups.
> >
> > Well the problem is, there are _no_ backups of the database or of the log.
> But as I understand it, since the log was not backup up
> > at all, it should contain all the transactions since the birth of the
> database. (The log file is some 5GB in size).
> >
> > I was hoping to accomplish this by starting with an empty database (the
> way it was when the log was started), and just applying the
> > complete log.
> >
> > I'm getting a sinking feeling that this may not be possible?
> >
> >
> >
> >
> >
>
>


 
 
Tibor





PostPosted: Wed May 19 01:51:07 CDT 2004 Top

SQL Server >> Partial Restore from Log <<Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the
log was not backup up at all, it should contain all the transactions since the birth of the database.>>

Above is a common misconception. Until you do your first database backup, the database is in "auto log
truncate mode" which means that SQL Server will truncate the log when 70% full or at checkpoint. So, you
cannot be certain to find your log records even when using some log reader tool.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/



> > The basic concept of getting this to work, is making sure you have a
> > complete sequence of backups.
>
> Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the
log was not backup up
> at all, it should contain all the transactions since the birth of the database. (The log file is some 5GB in
size).
>
> I was hoping to accomplish this by starting with an empty database (the way it was when the log was
started), and just applying the
> complete log.
>
> I'm getting a sinking feeling that this may not be possible?
>
>
>
>
>