How to rollback database only with MDF and LDF files but without backup file?

Question

Is it possible to rollback only with MDF and LDF files but without backup file?

I worked days but suddenly it's gone. It's important to me.

Do you have older versions of .mdf and .ldf files? This might be a starting point. Also this question might be useful. — Radu Gheorghiu
– Radu Gheorghiu, Commented Nov 8, 2016 at 10:18
I'm using MSSQL Server 2005 and the MDF, LDF files were recently created by this Server meaning not older version..I want to rollback the database to the timepoint of today's morning..Is it possible only with MDF, LDF files ? — Kay Lee
– Kay Lee, Commented Nov 8, 2016 at 10:24

Roger Wolf · Accepted Answer · 2016-11-08 10:45:48Z

Yes, it is possible, but only if:

Your database is in full or bulk-logged recovery mode, and
You had taken at least one full backup prior to the point of failure, and
There is a complete chain of transaction log backups since the last full backup, or
There were no transaction log backups since then.

What you need to do is:

Take transaction log backup of your database;
Restore your last full backup as a new database with NO_RECOVERY option;
Restore all necessary transaction log backups, if any, again with NO_RECOVERY;
Restore the most recent transaction log backup you just made in #1 with RECOVERY and STOPAT options. In the latter, you can specify the exact time on which you want your database to be restored.

For full syntax, see RESTORE.

Thanks for your good opinion. However, as stated in the question, I don't have any backup file for this database. I know how to backup & restore but not this specific database...any more positive idea?
None, I'm afraid. Having no full backup means that the database is essentially in simple recovery mode, so transaction log gets truncated on every checkpoint. The changes are simply not there anymore :(

1 Answer 1