-1

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.

TT.
  • 15,774
  • 6
  • 47
  • 88
Kay Lee
  • 922
  • 1
  • 12
  • 40
  • 1
    Do you have older versions of .mdf and .ldf files? [**This**](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/19f3cdf2-3b72-414f-ae18-724a2fe0401e/rollback-of-transactions-from-ldf-file?forum=sqldisasterrecovery) might be a starting point. Also [**this question**](http://stackoverflow.com/questions/16945254/how-to-perform-sql-roll-back-from-transaction-logs) might be useful. – Radu Gheorghiu Nov 08 '16 at 10:18
  • 1
    Can you elaborate your requirement little bit more – User Nov 08 '16 at 10:19
  • 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 Nov 08 '16 at 10:24

1 Answers1

0

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:

  1. Take transaction log backup of your database;
  2. Restore your last full backup as a new database with NO_RECOVERY option;
  3. Restore all necessary transaction log backups, if any, again with NO_RECOVERY;
  4. 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.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
  • 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? – Kay Lee Nov 08 '16 at 12:02
  • 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 :( – Roger Wolf Nov 08 '16 at 12:56