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?
A:
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!
Robin Day
2009-05-18 11:28:06
"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
2009-05-18 11:48:05
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
2009-05-18 12:16:28
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
2009-05-18 18:26:56
...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
2009-05-18 18:27:31
+2
A:
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
gbn
2009-05-18 11:49:34
A:
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.
richardtallent
2009-08-29 06:56:51