I have an SQL 2005 database with logging set to FULL but without any backups. Is it possible to restore it to a point of time?

  • There is another way to get a database in FULL recovery model to a point of time. It's not actually restoring it, but rolling back the transactions. As the database in FULL recovery model, all the transactions are still in the online transaction log, or in transaction log backups (if you create them). You can use some 3rd party tool to roll back up to a point in time. These tools are usually advertised for data recovery, but they actually create Undo scripts. – Carol Baker West Aug 16 '12 at 14:27

I had a database using the simple recovery model, that had never been backed up.

I deleted some things that I shouldn't have and came to this thread and after reading it my heart was in my throat thinking of #1 telling the client and #2 all the work I'd be putting in to replace the data.

I continued to have a look on google and found ApexSQL recover. Link

I detached the database, took a copy of the detached data and log files then reattached the original and then ran the program. I told it the database and pointed it at the copy of the detached log file. It found all the deletions and scripted the recovery.

All in all, I'm very happy with the product and would recommend it to anyone. There is a 14 day trial, but it only recovers 1 in 10 rows.

| improve this answer | |
  • When all things fall short, 10% is better than 0% but testing backups regularly is more important! – Nicolas de Fontenay Jan 27 '16 at 16:26

No backup = no restore. You restore from a backup you created earlier.

It makes no difference what recovery model you have.

Some myths busted from Paul Randall: BackupRestore

| improve this answer | |

In general to restore to a point in time, you take an old backup of a database, and restore transaction logs up to the point in time.

Whilst the database as you have it should in theory have all the data required to take the database from its beginning up to any point in time, I can't see any way of doing it!

You cannot create a transaction log backup without having a database backup.

My only idea is that you may be able to "trick" SQL in to thinking that it has a backup of the database when it was empty and applying the transaction log to that. I have no idea how to go about doing that though.

Sorry, this isn't an answer to your question, just putting my ideas down in case it may help. My instict would be that what you ask is simple, but on initial investigation it looks impossible!

| improve this answer | |
  • "As soon as you attempt to make a backup of your database, you will empty your transaction log making any chance of restoring to a point in time impossible." What? Prove it or delete it. – gbn May 18 '09 at 11:48
  • Deleted... I was just throwing ideas out there, wasn't meant to be a full answer. I always thought that once you made a database backup, any transaction log backup you made could only ever be from that point. I will have to check up more. – Robin Day May 18 '09 at 12:16
  • Maybe, it depends on your wording. A log backup is a record of committed transaction since your previous log backup. The log entries that are backed up are cleared out. A full backup does not clear the log entries (but it does includes data changes that occur during the full backup)... – gbn May 18 '09 at 18:26
  • ...so in theory,could apply a few thousand log restores since your full backup a year ago and skip any intermediate full backups because you have all log entries for the last year. I'm not sure recently, but you could backup the log without a full: it made no sense of course. Also if the recovery model changed between full backups you were bollixed too. – gbn May 18 '09 at 18:27

Transaction logs store the new information, they don't retain the old data.

E.g., if you change a record's FirstName field from "Joe" to "Bill", the transaction log only records that you changed record X's FirstName to "Bill," not that it was "Joe" before.

So, you can use them to move forward in time from a previous backup, but you can't use them to travel back in time.

The only thing you can do is theoretically determine which records were inserted or updated, but you can't determine what they were before that those transactions occurred.

| improve this answer | |

I have restored back to a point in time using nothing other than SQL's Restore and selecting a data and time to restore to. (Deleted some records that I shouldn't have) The restore was on the same day though as the point in time i restored to.

Now though I'm trying to do it again and alas it only goes to the last backup that was restored. Not sure what I have done differently :(

| improve this answer | |
  • If these are both your account, log into both sites with the same information and you can edit the question with no issues. – jcolebrand Jan 24 '13 at 2:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy