[CRITICAL] Transaction Logs and Restoring

[Grudge]

We had a problem in our Office System and one of our tables got completely deleted, our backups are totally screwed and I'm having a very bad friday...

The SQL server is still running, is it possible to reverse the delete by using the transaction log in some or other way. Where will I be able to find out from where this table got cleared. Any help on this would be greatly appreciated as my job is on the line here.

 

[Grudge]

Think I can fix it. How do I restore a transaction log up to a certain time. I've got a transaction log which I want to restore upto a certain time, lets say this morning. Is this possible ?

 

[jester777]

The RESTORE command has a STOP_AT parameter

STOPAT = date_time | @date_time_var

Specifies that the database be restored to the state it was in as of the specified date and time. If a variable is used for STOPAT, the variable must be varchar, char, smalldatetime, or datetime data type. Only transaction log records written before the specified date and time are applied to the database.

RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'

 

[Grudge]

>RESTORE LOG MyNwind
> FROM MyNwindLog2
> WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'

How would this syntax change if I want to restore from a transaction log file ?

 

[jester777]

That is restoring from a transaction log file, you can't do a database restore up to a certain time !.
In your first post you said the backups were screwed, does this mean you cannot go back to a DB dump to then apply the tran log dumps?

 

[Grudge]

I mean how can specify the actual transaction log file (file.ldf). I've created a new database with our latest db backup (about two months ago). And now i've backed up the transaction logs of the current db (with missin table). Now I want to get the new db (with the old backup ) updated upto the time I specify.

Hope this helps

 

[Grudge]

This is what I meant :

RESTORE LOG MainDBTest
FROM disk='p:\maindb_backup'
WITH RECOVERY, STOPAT = 'Mar 11, 2003 09:00 PM'

maindb_backup is a complete backup of the current database, I'm trying to get it to restore the db from the transactions log in the maindb_backup, but I get the following error :

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.

 

[jester777]

When you restore the DATABASE make sure you use the NORECOVERY option as part of the RESTORE statement. This should then allow you to RESTORE the transaction log dump.
The default for RESTORE is RECOVERY which means no more transaction log dumps can be applied (which I guess you did with the db restore).

 

[Grudge]

What does this mean ?

Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 3361000000110200001, which is too early to apply to the database. A more recent log backup that includes LSN 3362000000462000001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Also, the backup file I'm working from contains 4 backups. Can I specify in the query from which one to work ?

 

[jester777]

Looks like you are trying to restore a transaction log dump taken before the db dump you are trying to apply it to.
You have to make sure you apply the tran log dumps in the correct order starting with the first one after the db dump you are restoring.
The RESTORE command has a parameter called FILE, the following is from BOL

FILE = { file_number | @file_number }
Identifies the backup set to be restored. For example, a file_number of 1 indicates the first backup set on the backup medium and a file_number of 2 indicates the second backup set.

 

[JayKusch]

Grudge ... Quick question off the subject of getting restoring your TLogs to a point in time recovery ...

If you are unable to get the restore working correctly and if the table/data is worth $1200 buck to recover ... Lumigent Log Explorer 4.0 can recover that table in 5 minutes tops!!! I have a copy of it running on my server with 45 mission-critical DBs. Log Explorer has well paid for itself in recovering items we had updated incorrectly, deleted unwittingly and ammended unknowingly. Here is their link just in case ...

http://www.lumigent.com

$1200 bucks may be a bit but you could have your table back in place w/in a half hour from now and be ready for the next time an issue like this happens. Good Luck!!!

Thanks

J. Kusch

 

[SQLBill]

Grudge,

To RESTORE a database, these are the steps you need to take:

1. first restore a full backup and use the WITH NORECOVERY option.

2. then each transaction log made after that full backup has to be restored in order. For ALL but the LAST one, you need to use the WITH NORECOVER. On the LAST one you will use the WITH STOPAT option.

Take a look at the BOL, use the Index tab and enter RESTORE DATABASE.

-SQLBill
BOL=Books OnLine=SQL Server's Help
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

 

[Grudge]

Thanks for all the help sofar guys. I've tried just about everything and now I'm waiting. The db shows grey in the enterprise manager after applying a transaction log and it show loading in brackets after the dbname. Not sure if this is a good thing. It's taking quite a bit. Is this normal ?

 

[SQLBill]

On the last transaction log restore, did you use the option WITH RECOVERY or at least not use WITH NORECOVERY (default is WITH RECOVERY)?

If you restored the last transaction log and used WITH NORECOVERY, just do this;

RESTORE LOG dbname
WITH RECOVERY

-SQLBill