November 4, 2009 at 9:34 am
I've been asked to try and recover some data accidentally deleted by a user. This is a contact database with not that much transaction volume. The kicker is, it does not appear this database had ever been backed up, and it was in Simple recovery mode.
The transaction log file date goes back a month, so I'm wondering if there is any possibility of A) rolling back, or B) changing the database to Full recovery mode, take a backup of db/log and then restoring to a previous point. Would anything like that even be possible, or is this user simply SOL, and should take this as a dramatic lesson in ensuring you have backups?
I've never attempted a rollback before, but have done restores to a point in time -- but in those cases there was a good backup routine that had been in place. I feel I may be grasping at straws here, but want to be sure before telling this person they are hooped.
November 4, 2009 at 9:54 am
If the database is using the SIMPLE recovery model and you have no backups, I'm sorry to say that you aren't going to be able to recover the lost data.
You really need to make sure that a backup strategy is put into place. You may also want to look into changing the recovery model to BULK_LOGGED or FULL and implement transaction log backups as well.
November 4, 2009 at 10:22 am
This might help you avoid future problems:
-- Databases NOT Backed Up Since YYYY-MM-DD
select * from master..sysdatabases db
where (not exists
(select * from msdb.dbo.backupset
where db.name = msdb.dbo.backupset.database_name
and backup_start_date > '2009-11-02'))
order by name
November 4, 2009 at 10:23 am
I wouldnt like to say 100% that you cant get the data back but i dont know of any way in which you can. Without a full backup before the event to go back to there is nowhere to get the old data from. If the database was in full/bulk recovery model you could possibly have used a log reader tool to recover the data, but as your in simple and the log will have been checkpointed and truncated it wont be in there now. Would be interested to see if anyone has any other thoughts on how this could be possible.
November 4, 2009 at 11:07 am
Thanks all - that's about what I figured. This site had not been my responsibility to manage, I've just been asked to see if I can recover the deleted data.
I've been scouring their server and I think I've found some older backups, from mid- to late-September, and that may have to do. Not a SQL backup, they have something called MozyPro, and doing a search for the database name it does appear that the MDF got backed up through it at least once. Won't get them everything, but some should be better than nothing. I'll also set up a maintenance plan with full backup and at least hourly transaction log backups.
I was hoping that given there was no maintenance plan specified at all, and that the log file (don't know about the contents, but the actual LDF file itself) hadn't been modified in over a month, that perhaps there might be some way to get it to go back a few days. But if I read correctly in my searching on the topic of the transaction log, the contents or level of detail in the log is different between SIMPLE and FULL recovery modes...
Well, lessons learned for the user, and I'll see what I can get for them through this other, older backup I located.
Thanks again.
November 4, 2009 at 11:23 am
Animal Magic (11/4/2009)
If a DB is in full recovery and has never had a backup, the log will still be auto-truncating on checkpoint and there will be nothing in the log for a log reader to recover. It's only after a full backup that log records start to be retained.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy