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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
migrated from stackoverflow.com Jan 21 '12 at 15:03
This question came from our site for professional and enthusiast programmers.
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
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.
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!
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.
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 :(