Fortunately for me, the database was in an Availability Group so after recreating the LUN and attaching it, I restored the database from the read-only secondary. The steps I followed were:
- Didn’t panic
- Removed the database from the Availability Group (I’m not convinced this part was necessary, but I am loathe to try it again)
- Recovered the secondary database
- Removed the secondary database from log backups (we back up on the secondary)
- Created a copy_only full backup of the secondary database
- Created a copy_only log backup of the secondary database WITH NORECOVERY (this puts it in a restoring state)
- Restored both backups on the primary
- Recovered the original primary WITH RECOVERY
- Joined the database back to the Availability Group
- Added the secondary back to the log backup job
It was a fairly small database, so it was really fast and nobody even noticed anything had happened. When my team mates returned from lunch, we all had a good laugh over it and shared a few other war stories.
Yesterday may have been a Tuesday, but for me it was Monday all day long! I made a mistake that could have been a huge mistakes if our team was not so diligent about having redundancy in the database system (Availability Groups, backups, etc). The key here is that I didn’t panic, I worked out a solution logically in my head, and implemented it. We all make mistakes, but if you have prepared and can keep your cool and correct them, you’ll almost always come out of it looking good.
Brent Ozar
Great reminder that all of us make mistakes.
SQLSoldier
Thanks Brent.
Rick Willemain
Thank you ! Valuable advise, I think. Rick Willemain
SQLSoldier
Thanks Rick!
Don’t Panic – Curated SQL
[…] Robert Davis describes how he recovered from accidentally deleting a production-necessary LUN: […]
James
I once deleted a data file in Oracle. Didn’t even have to take it offline first.
Scott
I didn’t see in his article where the database was offline. Depending on his AlwaysOn setting the database would have failed over to the Secondary replica (read/write) automatically or manually. Perhaps he had two or more secondary replicas.
SQLSoldier
Our AGs are not configured to failover automatically so no failover occurred.
Terry Steadman
Greetings,
I had something similar to this happen at a prior work place. Except that it was a NAS system holding all virtual systems, including the SQL. I had a full virtual environment given to me for development and support purposes. I had also asked for backup for this system as well for in case something bad would happen. I.T. had told me that I didn’t need no backup. So, I worked for a year on a huge project involving the database and a whole new front end with hundreds of pages. I was about 2 months away from release to production. Just adding a few glossy touches to the whole system when all of a sudden *cue the horror music*, My whole virtual environment started getting major drive errors and then it crashed completely and would not reboot. From all appearances, the system never existed. I.T. went to work to “try to find out what happened”. I found out a few days later that the exact same person that said I didn’t need no stinkin backup for my virtual system had given all of my NAS drives away to another developer. This person had then immediately repartitioned the drives – resulting in a complete and total loss of *ALL* of my stuff. That includes the new database, the reports, front end and a years worth of development. *Umm – can you say “oops”?*. Needless to say, management wasn’t very happy with this type of mistake and I had the drives restored to me later on. I still had to rebuild the entire development system though. Oh, as for the huge project, I was able to release it on time and thus saving multiple collective butts. I didn’t spend 2 months staying awake all night rebuilding a years worth of work, I just restored most of my work from the impromptu backups I had made to my physical hard drive on my local pc. You know – just in case *something* might happen. 🙂
SQLSoldier
Wow, that’s quite the story, Terry. Thanks for sharing it!
SQLDBnVA
Like watch’n the pro’s on TV still make mistakes – We all do. Backups, AG, Mirroring, DR, etc. can be wonderful things
SQLSoldier
Absolutely!
Ron
01: Didn’t panic – That is definitely the best advice in any disaster situation!
SQLSoldier
Agree 100%. Being prepared and knowing you have protection in place will help you do that.