How to recover from an accidental restore

Question

I accidentally restored my last backup into the production database. The recovery model of the database is Simple.

Is it posible recover the data starting from my last backup and using data from the transaction log? I understand
restore operation truncates the log, but the transactional data is still present physically in the file. If somehow I
can recover all the SQL instructions from the truncated log and replay them to the last database backup, It would save my
life.

Answer 1

Hi Rodolfo28,

I am very sorry to tell you that you can’t recover the data starting from your last backup and use data from the transaction log.

Your database is set to the simple recovery model, it maintains only a minimum amount of information in the SQL Server transaction log file. SQL Server, on its own, truncates the transaction log files and removes the information related to transactions which have reached transaction checkpoints (data has been written to the data file) so that the space can be reused, leaving no transaction log entries for disaster recovery purposes.

It is not possible to restore such a database to a given point in time, you will not be able to perform the transaction log backup, and you may only restore it to the exact time when a full or differential backup occurred. Therefore, you will automatically lose any data modifications made between the time of the most recent full/differential backup and the time of the failure.

Regards,
Angelia

Answer 2

you can use the most recent full database backup if you have, and then restore all subsequent log backups.

Answer 3

Hi Jason,

I don't have bakups of the log, the recovery model is simple so I only have backups of database.

My only hope is to extract the data from the log file of the database that was truncated with restore operation, but I understand data is still physically there. Although, I haven't found a tool or a description of the format of the log file.

Answer 4

Hi,

Sadly, simple recovery means no log backup thus no log restore.

You can only rely on your latest full + latest differential.

Answer 5

Hi Rodolfo28,

Are you trying to fetch the data from transaction log file ?

Answer 6

Hi,

There's no useful data in the transaction log when the database is in simple recovery.