Recover from transaction log

Question

Recover from transaction log

darrylms

SSC Rookie

Points: 25
More actions
October 5, 2009 at 7:19 am

#226851

In SQL Server 2005, is there a way to recover to a specific point in time from the transaction logs, without having a backup of the DB available?
Red Gate has a product called SQL Log Rescue, which seems to be perfect for the job, but doesn't support SQL Server 2005.
Any ideas?
Thanks
Darryl

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Apex has a log reader that works for SQL2K5, I havent used it but it looks like it would work.

http://www.apexsql.com/sql_tools_log.asp?gclid=CNfQ0tn_pZ0CFWAB4wodAEaI1g"> http://www.apexsql.com/sql_tools_log.asp?gclid=CNfQ0tn_pZ0CFWAB4wodAEaI1g

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If there has never been a backup of the database, then no. Regardless of recovery model, if there has never been a backup of the database then the log will be in auto-truncate mode, meaning the inactive transactions will be cleared from transaction log when a checkpoint occurs.

To recover from the transaction log, you need one of the following:

Database in full (or bulk-logged) recovery, full database backup, all log backups since the full backup

Database in full (or bulk-logged) recovery, full database backup, log has not been backed up or truncated since the full backup, 3rd party log reader.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

Would a log reader tool be usefule for extracting transaction information if a full backup was taken, and log backups have been taken, but the full backup no longer exists ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

homebrew01 (10/5/2009)
Would a log reader tool be usefule for extracting transaction information if a full backup was taken, and log backups have been taken, but the full backup no longer exists ?

Kinda.

The log reader would be able to get the info since the last log backup. I don't offhand know whether the log reader tools can read log backups or just the active transaction log.

Without having the full backup you wouldn't be able to restore the log backups and, if the log reader can't read log backup files, it would only be able to get the data since the last log backup.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

I now seem to remember using Lumigent Log Explorer for something like this .... I think it built insert statements based on the deletes that occurred so you could recover deleted data for a particular table. Never really had much need for it since. To do point in time, you'd need all the logs, and it sounds like it would be very cumbersome (if even possible).

darrylms SSC Rookie Points: 25 More actions · Answer 6

I'm looking for a delete recovery on two or three tables, so Lumigent's tool *might* do the trick. Kinda hoping it'll work with the existing transaction log.

I'll let you know how it goes.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Why oh why oh why do you not have backups?

Do note that neither ApexSQL nor Lumigent's log readers are free.

Apex SQL Log: $999 per server

Lumigent Log Explorer: Starts at $1,400

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

darrylms SSC Rookie Points: 25 More actions · Answer 8

Well, there are backups.

As of yesterday. 😉

Some of the users created a new work area in one of their apps. The app decided to generate another DB for this.

I became involved when I was asked to restore the DB that hadn't been added to the backup list.

No use complaining about it, though. I'll have to monitor it more closely to prevent it from happening again.

But back to the data loss - the company has decided that the users can recreate the data themselves (will take a day or so) rather than spring for ApexSQL Log.

Thanks for all your help.

Much appreciated.

@SQLNumpty SSCrazy Points: 2940 More actions · Answer 9

darrylms (10/6/2009)
I became involved when I was asked to restore the DB that hadn't been added to the backup list.
No use complaining about it, though. I'll have to monitor it more closely to prevent it from happening again.

Sorry darrylms, don't monitor, just stop joe users from being able to create databases. This will save everyone a lot of trouble and data loss.

I'm assuming you are responsible for your SQL servers ...

homebrew01 SSC Guru Points: 55548 More actions · Answer 10

darrylms (10/6/2009)
Some of the users created a new work area in one of their apps. The app decided to generate another DB for this.
I became involved when I was asked to restore the DB that hadn't been added to the backup list.

One nice thing about Redgate backup software (and possibly others) is that the backup script can be written with EXCLUDE logic. So when it runs, it automatically backs up all databases except those specifically excluded. So any new database will automatically get backed up, unless you tell it not to.