November 28, 2007 at 10:03 am
I just made a bit of a mistake in a live database. Unfortunately the database is using the simple recovery model so tools like ApexSQL Log seem to not be an option. We're running SQL 2005 now, but on SQL 200 I distinctly remember Redgate SQL Log Rescue being able to use online transaction logs from the simple recovery model to restore data. Is that no longer possible with 2005? Redgate's tool only works against 2000, so I'm out of luck with that. Apex won't even try since we're using simple recovery.
It's frustrating knowing that the data I mistakenly wiped out is sitting in that 100 MB file on my disk but I just can't get at it. I was going to go as far as making a copy of the file and running the unix 'strings' command against it, but I can't copy a file that's in use...
If I switch to Full recovery right now, am I going to wipe out the current entries in the transaction log?
I really hate to go to the last backup, which was made last night.
Am I SOL and have I learned the hard way that I should be using something other than simple logging?
November 28, 2007 at 11:27 am
I really do not think there is much you can do, in this situation. The biggest problem you are faced with is the recovery model is set to SIMPLE and this recovery model truncates the transaction log. You should switch to the Full Backup model and perform regular full and tlog backups.
Alternatively, you can create a database snapshot before you perform any action on a production server.
Note: If any transactions have occurred after the creation of the snapshot, they will be lost. Also, all snapshots, other than the one you need, must be deleted before restoring from a snapshot.
You need to take a look at your business requirements to determine what is an acceptable amount of data loss, if any, and base your backup strategy on this information.
November 28, 2007 at 11:35 am
I decided to just do a test on a development database of switching from SIMPLE to FULL and it did preserve the transaction log entries. So I did this on the live server and was able to load up ApexSQL Log and get my data back!
Having done restores from SIMPLE transaction logs before, it seems like the transaction log is more of a round-robin type thing and it purges old transaction records as necessary. When I loaded up my database in ApexSQL Log I was able to see transactions as old as yesterday.
November 28, 2007 at 11:47 am
I am glad everything worked out! 😀
It seems like ApexSQL will not even consider looking into the tran. log of a database, if it is set to the simple recovery model.
November 28, 2007 at 11:51 am
Just for some good reading material this link has some good arguement about the different recovery models and their performance.
http://www.sqlservercentral.com/Forums/Topic426078-146-1.aspx
November 29, 2007 at 6:28 am
It is good you got back ur data. It may happes so the data are commited to the mdf file after chaging the recovery model you were able to run the tool and got back the data till that point. But Im bit confused. bcoz when change the recovery model the log sequence breaks...:unsure:
any one who can explain !!
"More Green More Oxygen !! Plant a tree today"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply