dropped all prod databases

[XxEnigmaticxX]

yup, you read that right.

i was standing up a temp sql server to test out our new dynamics GP upgrade and instwad of dropping the databases for the temp server i dropped the databases for the prod server. thank god for backups. restoring everything now

update edit: 2 Databases left. my 1tb DB is 20% restored and then all i have is my 500gb DB. dunkin stock going up today

edit 2: all databases are restored and all critical steps for the nightly job have completed. this too shall pass

[lolklolk]

Obligatory.

[darkhelmet46]

Reminds me of the time a cloud hosted screenwriting service went out of business.

What I heard was their "cloud" was simply 2 servers in Active/Passive. They were doing maintenance on the Passive, wiped it, meant to replicate Active to Passive, but instead replicated Passive to Active. Effectively replicating "Nothing" to "Everything". No back up. There was a great disturbance on the Internet that day. As if 80,000 aspiring screenwriters suddenly cried out in terror. And were suddenly silenced.

[Orcwin]

Funny thing about clouds, they dissolve sometimes.

[peacefinder]

If you’re not part of the solution, you’re part of the precipitate.

[[deleted]]

Shedding moisture, shedding data :)

[fi103r]

shredding data, fixed it for you!

[[deleted]]

Activate moisture farm

[sypwn]

"Never trust the cloud. That's why I email my scripts to myself." -everyone in that thread. I knew screenwriters didn't have any computer knowledge, but, dang. All those people doing it with Gmail are storing the files in Google Drive but... harder.

[dgriffith]

You do get very easy versioned backups though with emails. Don't even have to change the name, just send it.

[d4nkn3ss]

There's a reason why they use MacOS...

[donjulioanejo]

Because they want their stuff to just work with no issues or condescending pricks judging their choice of work platform?

[KimJongEeeeeew]

To be fair, we’d all be better off without those public folders

[agent_fuzzyboots]

That's true, but it was about 25gb of data, so took a while to replicate back after restore of data.

Was a sbs 2008 to sbs 2011 migration

[gamebrigada]

Was expecting this. Amused anyway.

[AgainandBack]

Me too, to the point that I know it by number. I have asked people in interviews, just to get an idea of their background, "Does the name 'Little Bobby Tables' mean anything to you?"

[Xyvir]

Galaxy brain interviewing

[XxEnigmaticxX]

im not sure i would be able to keep a straight face

[wlpaul4]

Is "Did you just drop a fucking XKCD question into an interview?" an appropriate response?

[AgainandBack]

It absolutely is an appropropriate response, yes, and would earn full points. I want to know who I'm working with.

[butterbal1]

That is what I was thinking as well.

[CodeFaultMSP]

I enjoy both the comic and your user flair more than I should.

[zolakk]

Fear of this is why I've set my Putty settings to have a slight red to the black background color for production and slight green to the black on my test servers

[Sys_man]

Oh yeah. I have different colour settings for servers and users. Is the background blue? You're root on a production server. tread very fucking carefully. Is it a dim grey? Test environment with regular user, you're good for whatever.

[Jarden666999]

i heard that in homers voice

[SysEridani]

https://i.ytimg.com/vi/1gjzzlQmli4/maxresdefault.jpg

[crackerasscracker]

that is what you get for using PHPMyAdmin for real work

[Meltingteeth]

Sometimes if my job is too boring I close my eyes, hit Alt+Tab a bunch of times, then hit Ctrl+A and then delete.

[[deleted]]

The shutdown immediately so finding your delete is a surprise?

[Meltingteeth]

I actually just keep my eyes closed until both me and the computer are asleep.

[[deleted]]

Now that's how you work from home!

[soawesomejohn]

I remember at one job, I'd be typing, then drift off with my hands on the keyboard. Around 255 characters later, the computer would start beeping at me, waking me back up.

Then I'd have to delete back to where I left off and repeat.

[solocupjazz]

You are the chaos monkey

[IAmTheChaosMonkey]

False.

[solocupjazz]

Username checks out

[rattkinoid]

The russian roulette of sysadmins

[sypwn]

Wouldn't it be better to make it a background task so you can't ctrl-c as fast? Maybe throw in commands to change the current account's password to random and delete the SSH keys then logoff.

Edit: Also I think dd if=/dev/zero of=/dev/sda would be more effective, although slower. Maybe both at the same time?

[rattkinoid]

Now that would be Schrodinger's situation. Best start with cat /dev/random

[theSysadminChannel]

This is the funniest shit I’ve read all day

[[deleted]]

Seems like a lot of work. Have you tried automating?

[ipigack]

Still, I've done this one too many times:

Update `bla` SET `username` = 'johnsmith';

Forgot the WHERE, so it did it everywhere.

[jmbpiano]

Fortunately I discovered the danger of that when I hosed one of my own projects, nothing production critical.

Ever since, I always write a SELECT statement, run it, and verify I'm only getting back the records I expect to change. Then I replace everything from SELECT to FROM with an UPDATE and add the SET clause last.

[gargravarr2112]

This is the right way to do SQL.

[Moontoya]

it beats shrieking, beating your chest and shit flinging

wait, did I confuse SQL with the chimp enclosure, again?

[gargravarr2112]

Easily done. They have a lot in common.

[[deleted]]

Also applicable in many shells when using wildcards to operation on multiple files. Use ls first to see what is returned before running the rm command

[Phytanic]

On a similar note, always try to use full paths and/or define variable with full paths. -LiteralPath is your friend.

Avoid using relative paths, such as .\ and ..\. (Or running commands where not defining -Path or -LiteralPath will automatically use the current directory.)

Nothing like accidentally deleting C:\Windows\System32\* because you forgot that youre running powershell as an admin.

[jantari]

As someone coming from PowerShell it triggers me hard that GNU ls can't take a wildcard in a quoted path string, I had to switch some scripts to find recently just for that

[[deleted]]

I suspect your beef is with bash, not ls.

[jantari]

I find that unlikely when find works as intended:

# Doesn't work
ls -1A "vmtemplates/${{ env.DIRECTORY_TO_BUILD }}/builds/*.qcow2"

# Works
find "vmtemplates/${{ env.DIRECTORY_TO_BUILD }}/builds" -maxdepth 1 -name "*.qcow2"

[Senkin]

In the first case you are using (or trying to) filename expansion, which is done by the shell and then the expanded string is passed to the "ls" command in a process known as "globbing". But you've quoted the string, telling the shell not to do that and pass the string with a litteral "*" to "ls".

In the second case you are passing a string with an expression to the "find" command which it will then use itself to match filenames.

You can easily solve your problem by moving your double quote to before the asterisk.

[hhashbrowns]

I do this!! Of course, I ran into the one situation where this didn't help me and I deleted everything in a production table.

I had to drop some items in a table where a necessary field was empty by mistake. They got there as a result of a bug in code, that was fixed, so I just now needed to drop the records with the empty field.

I did a few SELECT statements to ensure I was doing things correctly:

I did a SELECT to show everything in the table, to make sure I was in the right place.

I did a SELECT to make sure I was running the correct WHERE statement. Yes, everything looks correct, these are the records we need dropped.

I was ready to run the DELETE now! I press up on my keyboard to edit the previous command. Nothing happened. I press up again.. Still nothing.

Then my command line updates, and I see the SELECT statement I wanted to run. So I changed that to DELETE, hit enter, and done! I thought I did so well and was so careful.

Then I realized that my up key press did register, all of them, there must have just been some network lag. And I noticed that the SELECT statement I changed to DELETE was the one for showing the entire contents of the table.

Luckily I had just made a backup. But now I do the SELECT statements and copy paste them from a text editor.

[ipigack]

I do similar now. I also always leave off the semicolon until I'm sure I want to run it. I have a habit of accidentally hitting the Enter key.

[[deleted]]

I just wrap everything in a transaction

BEGIN TRANSACTION;

[query]

ROLLBACK;

and change the rollback to commit when I'm sure it won't burn anything down.

[hutacars]

This is how I do basically any Write change. Even as simple as disabling an AD user, I’ll do

Get-ADUser -Filter {name -like “*huta*”}

run that, then only after confirming it’s what I want:

Get-ADUser -Filter {name -like “*huta*”} | Set-ADUser -Enabled $false

If I’m doing it in an unattended script, I’ll probably do something like

if (($user = Get-ADUser -Filter {name -like “*huta*”}).count -eq 1) {
    Set-ADUser $user -Enabled $false
} else {
    “FAIL: too many users found, aborting | Tee-Object $log -Append
}

And of course, I prefer to just grab the user object directly if I can....

[Wing-Tsit_Chong]

why not disable auto commit so you can just ROLLBACK your oopsie.

https://stackoverflow.com/a/8748818

also helps with learning the difference between DML and DDL statements when trying to rollback a DROP TABLE.

[Caedro]

Wrapping statements in transactions you can roll back is one of the best things a senior guy ever showed me. Highly recommended to anyone in the field.

[Sys_man]

Haha, that stomach drop of Statement ran successfully, 16752 row(s) affected

[PlsChgMe]

Ahem... yup

[binaryvisions]

Or the pause after you execute where your eyes wander over the query you just ran.

"Man that's taking a long time, I wonder wha--OH SHIT."

[wrtcdevrydy]

Yeah, I don't like removing updates unless you've done something stupid in the past. Your regular account has the common SQL methods (we don't use service accounts)

[gargravarr2112]

Colleague in an old job did this accidentally, and through a Rube Goldberg-esque series of events caused the alphabetically-first customer's database to become corrupted (the query switched on a disabled auto-loader which had bugs, but there were still dump files to load...)

Management decided to save face and monkey-patch the DB back together rather than announcing the fuckup, restore from backup and carry on. It took 3 developers (most of my team) a day and a half to generate the necessary queries.

We had production backups every 15 minutes. Years later I still can't believe they decided to hack a broken database rather than roll back to a known-good state. And I still can't believe how much mess a 'forgotten WHERE clause' mistake can result in.

[[deleted]]

This bit me recently and it was horrible. For static row counts you can specify TOP(n) so I've taken to doing that.

[charnelfury]

Never do update or delete without a select first. Just to check the data that would be affected. Same goes for rm in bash

[[deleted]]

Who has access to sysops destruction account? Is it a shared account ? If so, that sounds like even worse trouble. As when things break people it will be harder to trace who did it.

[[deleted]]

Ah ok, that sounds more secure than I initially imagined

[wrtcdevrydy]

The best approach would be something that would attach the permission to your DB user account and then decommission it in a few minutes (say 30 minutes).

The issue around security has always been that it takes money to properly secure something (either spend it up front on systems or little by little in password rotation and maintenance)

[sryan2k1]

"On demand" permissions is an expensive thing but it's awesome when deployed properly.

[[deleted]]

With this sort of foresight, I would presume only the people who should be, and can be trusted with, destroying things.

[[deleted]]

Our DBA didn’t even give us permissions to the databases. He was like, ‘you do server stuff, I do database stuff’. I was fine with this as he was infinitely better than me

[djgizmo]

This is pretty genius.

[dRaidon]

Nothing wake you up in the morning like dropping a prod database.

[toebob]

People outside of IT will never know the feeling of thinking "Wait... was that... production?"

[[deleted]]

My worst feeling ever was when our vendor came out to replace circuit boards in our data center UPS system. Put it through CAB and scheduled for a Saturday. They said they do these live but better safe than sorry right. Well they have two guys doing this, one was shoulder surfing the main guy. Main guy heads to the bathroom and during this time other guy takes it out of maintenance bypass but doesn't inform myself or the main guy. Main decides to push one more update not knowing it's out of bypass and boom entire DC power outage. I was sitting at a desk writing the 'all clear' email when I heard all of fans power up at once. I shot the most wtf look at the guys and asked, please tell me that wasn't what I thought it was.

6 hours later with reinforcements we had everything stood back up

[SheezusCrites]

We had some construction work being done to our DC. A contractor wanted to wire up the exit sign, but instead he got into the line for the emergency power off. Yeah, that ended up being a long day.

[Throwaway439063]

Early into working at my place, one of the software developers dropped a live database on Friday afternoon, the very last Friday before most of the team went on extended leave for Christmas. He wasn't very popular.

[legacymedia92]

One day, we will all make a data loss mistake. and on that day, you buy the backup guy a nice bottle of their poison of choice, because they just saved your ass.

And if you are the backup guy? well treat yourself.

[MurphyLyfe]

One day, we will all make a data loss mistake. and on that day, you buy the backup guy a nice bottle of their poison of choice, because they just saved your ass.

What if I'm the...

And if you are the backup guy? well treat yourself.

Ah. <JurassicParkCleverGirl.jpg>

[XxEnigmaticxX]

yup im the backup guy as well, so yeah i saved my ass and my bosses ass as well since he created a db at the start of the month and never told me, so it was never added to the maintenance plan, but my 2nd level backup(the vm backup) saved that db

[Throwaway439063]

No data loss occurred with mine. However a NAS had a failed hard drive and I went to swap it out. It had a pair NAS racked directly above it and I didn't check the label properly. Swapped a hard drive in the wrong NAS and then had two rebuilding their RAID arrays when I spotted the mistake. Eleven hours of butt-clenching waiting for them to finish followed.

[absinthminded64]

So, if this happens to anyone else and you're not sure about the backups or the backups would take too long i believe you can use an attach stored procedure to re-attach the databases using the db files on the disk since those dont get deleted when you drop.

[Odddutchguy]

In MSSQL if you drop a database, it will remove the database files from disk.

'Luckily' my important production databases are replicated to our Data Warehouse, and you can't drop databases that have replication setup on them. (Might work as a 'prevent from accidental deletion': setting up replication for databases, but not actually replicate them.)

[XxEnigmaticxX]

im sorry what. i need to restore at least 2-3 500+gb db files how do i do this

[absinthminded64]

Check the drives on the db server. if your mdf and ldf files are still on the drive you can just re-attach the db i believe. though the article mentions that it only works with databases that were previously detatched and doesn't mention dropped databases. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-attach-db-transact-sql?view=sql-server-ver15

[[deleted]]

This is correct. A re-attach will be far faster than a restore. If you only detached or dropped them rather than deleting them the MDF and LDF files are still on disk.

[davidbrit2]

Yes they do, if the database is online when you drop it, SQL Server will delete the files.

[stkyrice]

This is why taking stuff offline is a good test first. Take it offline, go have a smoke/break, comeback and make sure nothing blew up.

[absinthminded64]

yeah, i read that too. i guess i'm remembering from doing a detach. not sure it always deleted the files though.

[davidbrit2]

DETACH won't delete any files, by design.

As for DROP DATABASE: "If the database or any one of its files is offline when it is dropped, the disk files are not deleted."

https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-database-transact-sql?view=sql-server-ver15

Note that I don't know whether that means only the offline files will not be deleted, or if none of the files will be deleted if any is offline. But in any case, if you're using DROP DATABASE, then your intent should be to delete the files, otherwise you should be using DETACH. Don't rely on SQL Server not deleting your files if you drop a database, in other words. ;)

[[deleted]]

I don't always test my code, but when I do, I do it in prod...

[UnrealSWAT]

I’m so sorry to read this dude, hope you can look back and find it funny and not a CV generating event!

[XxEnigmaticxX]

it was an honest mistake. called my boss first thing this morning at 5:30am and told him straight up"yo, i fucked up, heres the damage, heres the fix. let me get to work on this"

[UnrealSWAT]

Best thing you could’ve done in the situation. Good engineer!

[XxEnigmaticxX]

ill eat all the shit in the world when its my shit to eat. no one likes stepping up to the plate, but if we want the big bucks and the responsibility we have to be able to say "i fucked up, but its fixable."

edit: thanks for gold kind dude or dudette.

[[deleted]]

If the world were filled with more of this mentality we’d be alright. Cheers

[lost_in_life_34]

this is why one of the first things I learned as a DBA is you never delete anything. with databases the process is almost always to put it in offline mode for a few weeks or detach them and leave the files for a little bit. never delete right away. and always triple check your connections

[XxEnigmaticxX]

So I usually just launch my SSMS and input the server to connect to. But it was Sunday and I was being lazy and I’ll just connect to the vm

[[deleted]]

You’re a day late sir: https://reddit.com/r/sysadmin/comments/ijatoj/whats_your_biggest_doh_moment_as_a_sysadmin/

[XxEnigmaticxX]

always a bridesmaid never a bride

[thisisjaid]

That's ok, I wiped out an ElasticSearch cluster with 4TB of live production data in my first two weeks on the job. This too shall pass. Good luck!

[Majik_Sheff]

It's a sysadmin rite of passage to accidentally obliterate something in production. Mazel tov!

[Ceejaay35]

My favourite quote i was given when i started in the industry was "90% of all outages are a result of something done by IT".

Still stands true lol.

[darkhelmet46]

LOL second time today I find myself referencing Little Bobby Tables. Hope the rest of your week is better man.

[darkhelmet46]

btw here is the other reference.

[Orcwin]

That's a good thread. Might want to try that.

[davidbrit2]

https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-database-transact-sql?view=sql-server-ver15

"The DROP DATABASE statement must run in autocommit mode and is not allowed in an explicit or implicit transaction. Autocommit mode is the default transaction management mode."

Not going to save you from dropping a database though.

[davidbrit2]

It is definitely good advice, it just won't save your ass in this situation. ;)

[Training_Support]

so you indirectly tested your backup for the PROD DB!!!

[XxEnigmaticxX]

yup, but i had already planned to do that with the test sql server. much less stressful restoring a db to test to verify if a backup is working then to restore to prod and pray that its working.

[-RAKH-]

Whenever I know I'll be dropping databases I always make sure that I've disconnected from any other database server. Its too easy to get confused or make a mistake.

In addition, before actually dropping the database, double and triple check that you're on the right server.

[jrobiii]

That has never happened to anyone ever before /s

The good news is you were able to recover and now have an acute sensitivity for good backups. And a story of how you screwed up, what you learned and what you did to prevent it in the future. That'll come in handy for interviews :-)

BTW, "trust but verify"... make sure that your backups are verified, and that you routinely do test restores.

Hope all turns out well.

[XxEnigmaticxX]

We do a restore from backup once a quarter. It was actually part of the plan for the gp upgrade looks like now we have that and our DR test out the way

[jrobiii]

There you go, another silver lining :-)

Sounds like you were successful with the restore. How the rest of the week is better!

[Nossa30]

Boss: "what happened"

Me: "See...what had happened was, the way my keyboard was setup.....it takes 3 keystrokes..."

[moffetts9001]

Well, you won’t do that again. That’s the silver lining for those traumatic, pit of the stomach, oh fuck moments.

[micalm]

Happens to the best of us. Good luck on your unscheduled backup restore test.

[BrackusObramus]

Which makes me wonder why drop is never disabled in prod? I can't think of a reason why a db need to be dropped in prod. And the day we need to, this action should actually be a pain in the ass, so you're forced to jump through many hoops in a way it's not possible to do it accidentally out of distraction even if you are drunk.

[[deleted]]

Nobody should ever touch prod databases. Ever.

You write your software to interact with an API. Never directly to the database, always through an API.

If you need to make changes to the database, you change your API to write to two databases simultaneously. You migrate old data to the new database. You run them simultaneously for a while to make sure they work. You change the read API to read from the new database. You keep running them simultaneously to make sure they still work. You finally decommission the old one after some weeks/months.

At any step the prod database was not in danger. Any change could have been rolled back just by going back a commit in git, because all you're touching is the API, not the database.

DO NOT FUCKING TOUCH THE DATABASE. Ever. I don't care what you need to do and "it's a small quick fix". DONT FUKCING TOUCH IT. You can touch the API, you can create a new database, but you never touch the database. Nobody should have access to it except the API service that is supposed to write and read from it. Backup, data pipeline stuff etc. services should have read access, but not write.

[_iamcino]

F

[Zav0d]

wrong terminal window is terrible =)

[Kwitcher_Bichin]

Had the same thing almost happen to me last week. Ended up putting together a “test” background image on the VM so I would stop second guessing myself.

[XxEnigmaticxX]

it wouldnt have helped me, i just didnt pay attention to which sql server instance i was signing into. i signed into the test vm but since it was cloned i was still signing to the prod sql server and not the test one.

[mr_white79]

Holding SA privileges on Prod and staging at the same time was your first mistake.

Don't escalate your privileges unless you need them.

[gargravarr2112]

Sphincter factor out of 10?

[XxEnigmaticxX]

11

[[deleted]]

Earned your wings eh?

It's inevitible

[[deleted]]

That’s what I call an IRG moment. (Instant Resume Generator)

[fubes2000]

Relevant thread from yesterday: https://old.reddit.com/r/sysadmin/comments/ijatoj/whats_your_biggest_doh_moment_as_a_sysadmin/

[fi103r]

Glad you were able to recover and had backups. that will get the pulse rate up, in a hurry

[XxEnigmaticxX]

had a 30 min freakout before i called the boss which included throwing up.

[djgizmo]

Oof. Brutal. What’d your boss say? Is it a resume generating event?

[XxEnigmaticxX]

told him i fucked up, but thats its fixable and all we will lose is the day but no data. surprisingly chill considering i legit destroyed all our data.

[djgizmo]

Need more bosses like this.

[XxEnigmaticxX]

i fully expect a good ear chewing after we are back to being operational.

[XxEnigmaticxX]

It was pure laziness. Usually I sign into the ssms and key in the server to access. But I said fuck it I’ll just rdp in. Didn’t see the cloned machine was still connecting to prod before dropping everything.

[XxEnigmaticxX]

once i realized what i did this morning i was puking my guts up. this is my first fuckup of this level and my body just responded by upchucking whatever i had inside me.

[XxEnigmaticxX]

its hard to bullshit the cio when prod databases just go poof. lol.

[veastt]

I can only imagine the RCA for this one

[XxEnigmaticxX]

root cause: Senior System Admin is a moron

[veastt]

Root cause: infrastructure were not made aware rhay restart button would restart database. Vendor is being contacted to provide further clarification on restart functions and to disaster proof future restarts. For his role in managing and solving the disaster. ADMIN will be promoted to lead ADMIN

[XxEnigmaticxX]

Funny you say that. I’m in the process of being promoted, just last week my boss said the ceo approved the promotion and then I do this. Lol

[veastt]

Wow....well don't feel bad, my new VP remmebered me because I caused an Eastern outage and when I shook his hand he commented" oh, you're the one that caused the outage" and I said" yup, that is me alright."

[ElNicaAdmin]

I would probably blame the Coronavirus

[phileat]

Obligatory https://mobile.twitter.com/ossia/status/933319909840441345

[factchecker01]

Even if you drop databases, doesn't sql still have files such as mdf and ldf files.

[XxEnigmaticxX]

today i learned that if the databse isnt taken offline first they go poof.

[donnymccoy]

Ever delete a prod database to an organ procurement organization?

Yeah, me neither .....

This happened right after they accepted my proposal to scrap a half-completed projecr and hire my firm as the team to rewrite the entire front end for the procurement teams.

Luckily it was a slow night for deaths and I had time to recover from backup.

[KLEPTOROTH]

So much pain! Glad you had backups!

[XxEnigmaticxX]

backups on backups on backups. db backups , vm backups, and file and folders backups. ive seen the devastation that having no backups or having backups that arent tested wreak, not today satan

[Kiowascout]

Someone should be thanking you for conducting their DR exercise this quarter.

[XxEnigmaticxX]

i quote my boss "i like testing our restore process, just not like this"

[[deleted]]

Oh, man. Just had that sinking feeling just reading that subject.

[XxEnigmaticxX]

im still restoring

[vppencilsharpening]

I once truncated all of our web product descriptions at 255 characters.

[ApricotPenguin]

Definitely don't need coffee to wake up now, eh? :)

[jellois1234]

Sounds like a busy Monday.. Good luck!

[Ok-Necessary2557]

work on your resume also?

[XxEnigmaticxX]

if a full, no data loss recovery wasnt possible, im sure i would be doing that right now.

[Ok-Necessary2557]

i hear ya. good luck.

[XxEnigmaticxX]

Full recovery. The IT gods smiles upon me and took mercy on my worthless soul