restoring from transaction log-no initial backup

[jcaulder]

Ok, I know this isn't supposed to be possible but I'm wondering if anyone knows a hack for this.

I have a database that has never had a backup performed on it. However, it was in full recovery mode and had the transaction log running the entire time without ever being truncated. So, all my transactions, and therefore data, is in the transaction log but I cannot restore it because I don't have an initial full backup.

Is there any way to 'trick' sql server into letting me restore the transaction log somewhere, perhaps a test database?

The database controls a web portal system that connects to various other databases to present data to the user. The vendor told us there was no need to backup because there was nothing of value in the database, only statistical information and profiles that could be easily rebuilt. However, we found that to be completely FALSE when someone deleted a profile and subsequently deleted a handful of patient clinical notes that were stored in this database.

My goal is to somehow get these notes that were deleted from the transaction log file so they can be re-entered back into the production system.

Any sql guru's got any ideas? I have to come up with a solution for this.

TIA!

 

[SQLRickster]

Only backup files can be restored. Backup file are a compressed file format an look nothing like the database or backup files that are mounted. My suggestion is this. Backup the database and transaction log. This will create a backup file for the database and the log file. Restore the database and apply the transaction log to a new database by using the "restore as" and pointing it to the backup file that you just created. This will give you a copy of the database without harming or overwritting the current production database. This should give you back the what was deleted.

Be careful in backing up the database. Once the database and log file have been backed up, the transaction log WILL BE TRUNCATED automatically by SQL, so the only saving grace is that the data should be contained in the backup file and should be there when you restore it.

Good Luck!
SQLRickster

 

[jcaulder]

Thanks for the reply!

The database and transaction log were backed up as soon as the problem was recognized. However, this was 5 days after the deletion occurred.

The db backup will only contain the data after the deletion right? Only the transaction log contains the data prior to deletion but I can't do a 'point in time' restore without having a backup dated before the deletion can I?

If I had a backup prior to the deletion, I could use that and the point in time restore from the transaction log backup to restore just prior to the deletion. As it is, I can only restore back to the first db backup or any point thereafter which is after the deletion which doesn't help.

Is this correct or are you saying there is some way to restore prior to the first backup of the database?

Thanks again!

 

[jcaulder]

Let me see if I can explain this in more detail. I am hoping someone can help because otherwise, we have a serious problem.

After looking at the backups, here's what it looks like:

Full Backup - 05/01/2003
****data deleted errantly on 12/02/2003*****
Full Backup - 12/08/2003 2:59PM 6.5MB
Full Backup - 12/08/2003 3:38PM 6.5MB
Full Backup - 12/09/2003 2:00AM 6.5MB
Tlog Backup - 12/09/2003 9:38AM 1.6GB
Tlog Backup - 12/09/2003 4:00PM 2.8MB

I cannot restore using the full backup from 5/1/2003 in combination with a point in time tlog restore from 12/9/2003 to get back to 12/2/2003 can I? From the size of the tlog backup, all the data is there but I can only use it with the latest prior backup from 12/9/2003. This does me no good since I need to restore to 12/2/2003.

Any options anyone can think of?

 

[SQLBill]

Try this:

RESTORE full backup 05/01/2003
RESTORE Tlog 12/09/203 WITH STOPAT '2003-12-02'

Refer to BOL for information on WITH STOPAT option of RESTORE LOG.

Since that's the FIRST Tlog you've ever done, it should have all the transactions since the initial FULL backup. The exception is if you had the database in SIMPLE recovery mode or Truncated the log at any time prior to the log backup.

-SQLBill

 

[jcaulder]

This sounds like exactly what I want. Using Enterprise Manager I could not use the 5/1/03 full backup with the 12/9/03 tlog backup. The point in time restore would not let me choose a date prior to 12/8/03.

I will try it in Query Analyzer using your suggestion with the WITH STOPAT option. I felt much better when I found that initial backup on 5/1/03. I didn't think there was any backup. Perhaps this will get it back.

Thanks for the suggestion! I will post back after I try it.

 

[jcaulder]

Ok, according to Query Analyzer the restore of the database and transaction log was successful. However, I went into Enterprise Manager and the database I was restoring is marked as 'loading' so I cannot access it. I've waited about 10 minutes now and it still says 'loading'. I checked the physical file sizes of the transaction log and datafile after the restore and they appear to be the correct size for a successful restore.

Is there something I need to do to take it out of loading mode? I ran the database restore with the 'norecovery' option and the final transaction log restore with 'recovery' option.

What else is there to do please?

Thanks!

 

[SQLBill]

Try running:

RESTORE LOG dbname WITH RECOVERY

Nothing else but that and that should do it.

(I've seen this issue mentioned before and it always seems that for some reason the WITH STOPAT causes the database to think there is still more coming. A bug-maybe?)

-SQLBill

 

[jcaulder]

Well, it looks like I'm back in business. The database is accessible again and appears to be restored back to 12/2/03. I won't know for sure whether the whole problem is solved until the vendor tries to extract the deleted notes and import them to production.

But it certainly looks VERY GOOD to me! I think this story may have a happy ending after all!


Thanks so much for your help!!