Sql-server – Restoring from an LDF file

restoresql server

A record was accidentally updated which I need to be restored. Now, please bear in mind I'm not super-experienced in restore/backing up databases…

I have a 7gb MDF file and a 10gb LDF file (Sql Express 2016) . The database's recovery mode was set as Simple – I have since changed this to FULL and performed a backup of the Transaction LOG.

I have restored the DB's original backup file (some 6 months ago – this is a development Sql Instance, so no backups are regularly taken of it). but when I try to restore the log file, I can select no timeline from the past.

My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file and if not, what possible use does this LDF file serve if I can't restore from it?

Thanks

Best Answer

My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file

No, You are in simple recovery, once the transaction is committed it is not kept in the .ldf there are no point in time recovery options.

and if not, what possible use does this LDF file serve if I can't restore from it?

You do not differentiate between the allocated and used space on .ldf file. My best guess is that long ago it was in full recovery, and no one was doing t-log backups so it grew huge. Then is was changed to simple recovery and it became mostly empty. See related Why Does the Transaction Log Keep Growing or Run Out of Space?

Related Question