April 23, 2013 at 8:14 am
Hi,
Have a database that is set to recovery model=full. Some data was inserted then immediately deleted, is it possible to get the inserted data back?
A Full backup has been done since this happened so I'm assuming that the tail of the transaction log has now been "flushed".
It was a Sql 2005 express installation.
April 23, 2013 at 8:20 am
Do you have the previous full backup and all transaction log backups that cover the insert and the delete?
April 23, 2013 at 8:22 am
The database was created, the data inserted and then deleted and then a full database backup performed. There was no differential and/or transaction log backups performed.
April 23, 2013 at 8:26 am
Hi,
In that case I don't know of a way to retrieve your data I'm sorry. You needed a full backup of the DB after you created it in order to be able to recover it.
Thanks,
Simon
April 23, 2013 at 8:28 am
To recover that data, you'd need to be able to restore (via log backups) to a point between the data being inserted and being deleted. Since you don't have those backups, the data is gone.
Got nothing to do with the full backup that ran, has to do with the fact that you didn't have a full backup run before the delete, so the database was in 'pseudo-simple' recovery model, log getting truncated on checkpoint as it would be in simple recovery. The log only starts being retained after the first full backup runs.
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
April 23, 2013 at 8:29 am
Hi,
That's what I thought.
I do have a full backup but as I'm trying to recover rows that were inserted and deleted within seconds I'm assuming they are gone for good.
If a full backup had not been performed would these rows be in the tail?
April 23, 2013 at 8:38 am
As I said, the full backup is not what caused that data to not be restorable.
To restore that, you need to have a full backup that was run before the data was inserted and deleted, which you said you did not have. Before the first full backup that is run on the database (which according to what you said was run after the data was deleted), the database behaves as if it is in simple recovery mode, meaning that the log is automatically truncated.
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
April 23, 2013 at 8:51 am
Sorry that was not quite correct.
Here is the actual sequence.
Database was backed-up/restored to this server.
Data was inserted/deleted
Full backup taken.
April 23, 2013 at 9:04 am
OK, you should be in business. Back up your transaction log. Restore your full backup (preferably not overwriting the existing database) with no recovery. Restore the log backup you just took with STOPAT between the time of the insert and the time of the delete. Recover your new database.
John
April 23, 2013 at 9:11 am
When you say "restore your database" do you mean from the original backup or from the one taken after the insert/delete occurred?
As I'm typing that it sounds like a dumb question but just need to be sure.
April 23, 2013 at 9:13 am
The original backup.
John
April 23, 2013 at 9:28 am
In that case, if you know the exact time that the rows were deleted you should be able to take a log backup now, restore teh initial backup WITH NORECOVERY and then restore the log backup with STOPAT and specify the time.
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
April 23, 2013 at 12:23 pm
Thanks for all the responses.
Problem I have is that lots of rows got inserted/deleted over the course of five days. Always it was insert/delete, so I'm thinking that the stopat is not going to help an d that I will need to buy a tool for this. I thought Redgate did some thing but it looks to be obsolete so I'm looking at http://www.apexsql.com instead. Their product allows selective restore from a log, i.e. all the inserts or all the deletes.
Any other tools anyone knows of that do this?
April 23, 2013 at 12:28 pm
If you know the time of the deletes that you want to 'undo' (or you can work it out), STOPAT will most definitely work. If you want to go buy a $1000 tool instead, up to you.
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
April 23, 2013 at 12:54 pm
I'm with Gail. Restore using the STOPAT and get the data recovered that you need. Far cheaper and imho - more reliable.
If you feel you need data that has been inserted, or that data that was later deleted should remain deleted - then you should restore to a different "new" database so you can pick out the data that can then be re-inserted back into the database in question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply