transaction backups w/no bak in the file set

[MacolaHelp]

Last night I did a restore from my primary database to a test database & chose the option to make the target database nonoperational & allow restore of transaction logs. To make a long story short, the process appeared to complete, even though the restore progress bar didn't start up right away. I chose a bak file dated 200311161900.bak & the trn files dated 200311170600, 200311171000, 200311171400 & 200311171800. I left at 6:30 last night after the process indicated it completely successfully.

After logging in this am to attempt to work on our primary db, we discovered that the db was empty & no records on file. In addition the bak file I started with is now gone. The bak file created at 7pm last night is 48kb instead of the 1.3 megs it typically is. The sql full backup runs each night including weekends, but the tape backup doesn't run Sat/Sun nights, so the only file I can restore from tape is the 200311141900.bak.

It does restore, but I am unable to apply the transaction logs from the 17th to this database. We tried to rename the 200311141900.bak to 200311161900.bak & it restores but starts returning errors with the transaction logs. I tried the other way, too, leaving the bak file as is & making the trn logs referencing the 1115 date. No dice.

It isn't looking very hopeful that the trn files I have will do any good. The client has the sql backup set to delete the prior day's bak & trn files because he was quickly running out of hard drive space.

Anybody got any tricks up their sleeve? Otherwise, I'll have to re-invent all the work I did yesterday. Sigh.

 

[mrdenny]

In order to apply the transaction logs from the 17th, you would need all the transaction logs starting at 2003-11-14 1900 hours. You can't apply transaction logs out of order.

If sounds like you will need to apply the most recient backup from tape, and re-do the work.

If the transaction log is still there, you might be able to toll back the transaction that deleted the data. Log Explorer might be of help.

http://www.lumigent.com/products/le_sql/le_sql.htm

But it depends on how the data was deleted from the database. If the tables were truncated, then there is no transaction. But if they were deleted, then there will be a transaction that can be rolled back, assuming that the log is still intacted.

Hope that helps.

Denny