March 3, 2018 at 6:24 am
Good Morning Experts,
We are taking full backup every saturday at 9PM, differential backup daily at 9PM and transaction log backups every 30 minutes.
A developer accidentally deleted data from table at 2:45 pm. He realized it at 3 pm and came and reported the issue to me at 3:10 pm. Could you please advise me the steps i need to do to bring the database just before the delete occurred.
March 3, 2018 at 6:30 am
Restore the last full backup before the data was deleted WITH NORECOVERY, then restore the last differential backup WITH NORECOVERY, then restore all log backups after that point WITH NORECOVERY, STOPAT and the full date and time you want the restore to stop at. 2:44 if you trust the developer's 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
March 3, 2018 at 6:52 am
GilaMonster - Saturday, March 3, 2018 6:30 AMRestore the last full backup before the data was deleted WITH NORECOVERY, then restore the last differential backup WITH NORECOVERY, then restore all log backups after that point WITH NORECOVERY, STOPAT and the full date and time you want the restore to stop at. 2:44 if you trust the developer's time.
Restore last full backup with norecovery
Restore last diff backup with norecovery
Restore all log backups taken taken after that and come till 2:30 PM log backup with norecovery
Restore 3:00 PM log backup, "stopat" 2:44:59 PM WITH RECOVERY
Am I Correct Gail?
March 3, 2018 at 7:56 am
coolchaitu - Saturday, March 3, 2018 6:52 AMGilaMonster - Saturday, March 3, 2018 6:30 AMRestore the last full backup before the data was deleted WITH NORECOVERY, then restore the last differential backup WITH NORECOVERY, then restore all log backups after that point WITH NORECOVERY, STOPAT and the full date and time you want the restore to stop at. 2:44 if you trust the developer's time.Restore last full backup with norecovery
Restore last diff backup with norecovery
Restore all log backups taken taken after that and come till 2:30 PM log backup with norecovery
Restore 3:00 PM log backup, "stopat" 2:44:59 PM WITH RECOVERYAm I Correct Gail?
Understand that restoring over the original database using such a method WILL RESULT IN ALL WORK IN THE DATABASE AFTER 3PM BEING TOTALLY LOST. My recommendation is to restore the data using the method that Gail spoke of but TO A DIFFERENT DATABASE and then make corrections to the original from that extra restored database.
As a bit of a side bar, if this happened on a production database, then you folks really need to make a change, especially if there's any PII or any other sensitive data in the production databases (and there usually is). Developers simply shouldn't be making direct changes to production nor have the access permissions to do so. If you ever need to pass any kind of a compliance audit, you will fail miserably if Developers have such unchecked privs. Even if you're guaranteed to never have such an audit in production, you've also just found out another good reason to avoid anyone (not just Developers) having such unchecked access.
If this occurred on a Development database, then good job having "production quality" backups on the Dev box. You'll still lose everything after 3PM but that's normally tolerable on a Dev box. I suspect, however (mostly because a lot of people don't actually treat their Dev boxes with such regard), that this occurred on a production box and that's the reason for my stern advice on this thread.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2018 at 8:19 am
Jeff Moden - Saturday, March 3, 2018 7:56 AMcoolchaitu - Saturday, March 3, 2018 6:52 AMGilaMonster - Saturday, March 3, 2018 6:30 AMRestore the last full backup before the data was deleted WITH NORECOVERY, then restore the last differential backup WITH NORECOVERY, then restore all log backups after that point WITH NORECOVERY, STOPAT and the full date and time you want the restore to stop at. 2:44 if you trust the developer's time.Restore last full backup with norecovery
Restore last diff backup with norecovery
Restore all log backups taken taken after that and come till 2:30 PM log backup with norecovery
Restore 3:00 PM log backup, "stopat" 2:44:59 PM WITH RECOVERYAm I Correct Gail?
Understand that restoring over the original database using such a method WILL RESULT IN ALL WORK IN THE DATABASE AFTER 3PM BEING TOTALLY LOST. My recommendation is to restore the data using the method that Gail spoke of but TO A DIFFERENT DATABASE and then make corrections to the original from that extra restored database.
As a bit of a side bar, if this happened on a production database, then you folks really need to make a change, especially if there's any PII or any other sensitive data in the production databases (and there usually is). Developers simply shouldn't be making direct changes to production nor have the access permissions to do so. If you ever need to pass any kind of a compliance audit, you will fail miserably if Developers have such unchecked privs. Even if you're guaranteed to never have such an audit in production, you've also just found out another good reason to avoid anyone (not just Developers) having such unchecked access.
If this occurred on a Development database, then good job having "production quality" backups on the Dev box. You'll still lose everything after 3PM but that's normally tolerable on a Dev box. I suspect, however (mostly because a lot of people don't actually treat their Dev boxes with such regard), that this occurred on a production box and that's the reason for my stern advice on this thread.
Thanks for the golden advise Jeff
March 3, 2018 at 9:00 am
coolchaitu - Saturday, March 3, 2018 6:52 AMGilaMonster - Saturday, March 3, 2018 6:30 AMRestore the last full backup before the data was deleted WITH NORECOVERY, then restore the last differential backup WITH NORECOVERY, then restore all log backups after that point WITH NORECOVERY, STOPAT and the full date and time you want the restore to stop at. 2:44 if you trust the developer's time.Restore last full backup with norecovery
Restore last diff backup with norecovery
Restore all log backups taken taken after that and come till 2:30 PM log backup with norecovery
Restore 3:00 PM log backup, "stopat" 2:44:59 PM WITH RECOVERYAm I Correct Gail?
Assuming you trust the developer's statement that he deleted data at precisely 2:45:00 PM (and I have never heard of a developer noting the time they accidentally delete data at all, much less to that precision, which makes me suspicious)
Is this an interview question?
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
March 4, 2018 at 6:05 am
GilaMonster - Saturday, March 3, 2018 9:00 AMcoolchaitu - Saturday, March 3, 2018 6:52 AMGilaMonster - Saturday, March 3, 2018 6:30 AMRestore the last full backup before the data was deleted WITH NORECOVERY, then restore the last differential backup WITH NORECOVERY, then restore all log backups after that point WITH NORECOVERY, STOPAT and the full date and time you want the restore to stop at. 2:44 if you trust the developer's time.Restore last full backup with norecovery
Restore last diff backup with norecovery
Restore all log backups taken taken after that and come till 2:30 PM log backup with norecovery
Restore 3:00 PM log backup, "stopat" 2:44:59 PM WITH RECOVERYAm I Correct Gail?
Assuming you trust the developer's statement that he deleted data at precisely 2:45:00 PM (and I have never heard of a developer noting the time they accidentally delete data at all, much less to that precision, which makes me suspicious)
Is this an interview question?
It is also an interview question Gail
March 4, 2018 at 5:17 pm
coolchaitu - Sunday, March 4, 2018 6:05 AMGilaMonster - Saturday, March 3, 2018 9:00 AMcoolchaitu - Saturday, March 3, 2018 6:52 AMGilaMonster - Saturday, March 3, 2018 6:30 AMRestore the last full backup before the data was deleted WITH NORECOVERY, then restore the last differential backup WITH NORECOVERY, then restore all log backups after that point WITH NORECOVERY, STOPAT and the full date and time you want the restore to stop at. 2:44 if you trust the developer's time.Restore last full backup with norecovery
Restore last diff backup with norecovery
Restore all log backups taken taken after that and come till 2:30 PM log backup with norecovery
Restore 3:00 PM log backup, "stopat" 2:44:59 PM WITH RECOVERYAm I Correct Gail?
Assuming you trust the developer's statement that he deleted data at precisely 2:45:00 PM (and I have never heard of a developer noting the time they accidentally delete data at all, much less to that precision, which makes me suspicious)
Is this an interview question?It is also an interview question Gail
You mean that you didn't actually have this problem where you work?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2018 at 6:25 pm
hahaha im really curious to see if this is an actual problem or interview question. 🙂
March 4, 2018 at 10:26 pm
Jeff Moden - Sunday, March 4, 2018 5:17 PMcoolchaitu - Sunday, March 4, 2018 6:05 AMGilaMonster - Saturday, March 3, 2018 9:00 AMcoolchaitu - Saturday, March 3, 2018 6:52 AMGilaMonster - Saturday, March 3, 2018 6:30 AMRestore the last full backup before the data was deleted WITH NORECOVERY, then restore the last differential backup WITH NORECOVERY, then restore all log backups after that point WITH NORECOVERY, STOPAT and the full date and time you want the restore to stop at. 2:44 if you trust the developer's time.Restore last full backup with norecovery
Restore last diff backup with norecovery
Restore all log backups taken taken after that and come till 2:30 PM log backup with norecovery
Restore 3:00 PM log backup, "stopat" 2:44:59 PM WITH RECOVERYAm I Correct Gail?
Assuming you trust the developer's statement that he deleted data at precisely 2:45:00 PM (and I have never heard of a developer noting the time they accidentally delete data at all, much less to that precision, which makes me suspicious)
Is this an interview question?It is also an interview question Gail
You mean that you didn't actually have this problem where you work?
We had this problem at work Jeff. One of my friend went for an interview and was asked this surprisingly.
March 5, 2018 at 12:30 am
Ivan R. - Sunday, March 4, 2018 6:25 PMhahaha im really curious to see if this is an actual problem or interview question. 🙂
This one's an interview question. Similar things may have happened (hell, I've had this enough times), but this one's from an interview
Giveaways: Very round times (delete data at 2:45 precisely??? Did the developer wait for the clock to strike exactly quarter to the hour before he hit delete), a developer that actually noted the exact time that he deleted data????
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
March 5, 2018 at 6:12 am
coolchaitu - Sunday, March 4, 2018 10:26 PMJeff Moden - Sunday, March 4, 2018 5:17 PMcoolchaitu - Sunday, March 4, 2018 6:05 AMGilaMonster - Saturday, March 3, 2018 9:00 AMcoolchaitu - Saturday, March 3, 2018 6:52 AMGilaMonster - Saturday, March 3, 2018 6:30 AMRestore the last full backup before the data was deleted WITH NORECOVERY, then restore the last differential backup WITH NORECOVERY, then restore all log backups after that point WITH NORECOVERY, STOPAT and the full date and time you want the restore to stop at. 2:44 if you trust the developer's time.Restore last full backup with norecovery
Restore last diff backup with norecovery
Restore all log backups taken taken after that and come till 2:30 PM log backup with norecovery
Restore 3:00 PM log backup, "stopat" 2:44:59 PM WITH RECOVERYAm I Correct Gail?
Assuming you trust the developer's statement that he deleted data at precisely 2:45:00 PM (and I have never heard of a developer noting the time they accidentally delete data at all, much less to that precision, which makes me suspicious)
Is this an interview question?It is also an interview question Gail
You mean that you didn't actually have this problem where you work?
We had this problem at work Jeff. One of my friend went for an interview and was asked this surprisingly.
So you didn't actually have to do a restore at work, right? You just have a friend at work that asked the question. And, no... questions about restores should never be a surprise on an interview and they should definitely never be a surprise if you actually need to do one at work. You need to be prepared at all times and the only way you can do that is to practice restores on a very regular basis.
The reason why I'm hounding you a bit about this is because if you good folks had to ask a question on a forum to help your buddy, it means that none of you actually know how to do one of the most critical things there is about the job. I suggest you start practicing until you actually start dreaming about it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2018 at 9:44 am
Jeff Moden - Monday, March 5, 2018 6:12 AMcoolchaitu - Sunday, March 4, 2018 10:26 PMJeff Moden - Sunday, March 4, 2018 5:17 PMcoolchaitu - Sunday, March 4, 2018 6:05 AMGilaMonster - Saturday, March 3, 2018 9:00 AMcoolchaitu - Saturday, March 3, 2018 6:52 AMGilaMonster - Saturday, March 3, 2018 6:30 AMRestore the last full backup before the data was deleted WITH NORECOVERY, then restore the last differential backup WITH NORECOVERY, then restore all log backups after that point WITH NORECOVERY, STOPAT and the full date and time you want the restore to stop at. 2:44 if you trust the developer's time.Restore last full backup with norecovery
Restore last diff backup with norecovery
Restore all log backups taken taken after that and come till 2:30 PM log backup with norecovery
Restore 3:00 PM log backup, "stopat" 2:44:59 PM WITH RECOVERYAm I Correct Gail?
Assuming you trust the developer's statement that he deleted data at precisely 2:45:00 PM (and I have never heard of a developer noting the time they accidentally delete data at all, much less to that precision, which makes me suspicious)
Is this an interview question?It is also an interview question Gail
You mean that you didn't actually have this problem where you work?
We had this problem at work Jeff. One of my friend went for an interview and was asked this surprisingly.
So you didn't actually have to do a restore at work, right? You just have a friend at work that asked the question. And, no... questions about restores should never be a surprise on an interview and they should definitely never be a surprise if you actually need to do one at work. You need to be prepared at all times and the only way you can do that is to practice restores on a very regular basis.
The reason why I'm hounding you a bit about this is because if you good folks had to ask a question on a forum to help your buddy, it means that none of you actually know how to do one of the most critical things there is about the job. I suggest you start practicing until you actually start dreaming about it.
Thanks for your valuable inputs Jeff
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply