Not long ago, a crack team of SQL Server experts was flamed for a crime against database normalization they didn't commit. These men promptly escaped from the taunts of academic relational theorists. Today, they survive as website editors and software evangelists. If you have a problem, if no one else can help, and if you can find them, maybe you can hire the DBA Team.
"Our rivals are going to be sweating this weekend." Bruno laughed mirthlessly from the shade of the canopy of the café and had a sip of the wine.
"It's sure hot, boss."
"It's not the heat that will make them sweat, but the nasty surprise I have planned, Gabriel." Bruno put down his drink and glanced across the square at the headquarters of Acme Logistics. "Yeah, I don't think they'll survive this. I'm going to own the only Logistics company in town."
Scene 1: Piña Colada in the server room
"Piña Colada in the disk drives?" Manuel, the CIO of Acme Logistics, stared out of the server room window across the town square, shaking his head in disbelief. How could a DBA do such a thing? It was breaking a taboo.
Developer João looked up while scraping together the broken bottles on the floor. "He was working late on Friday, as normal, but then he seemed to flip, destroyed the server and vanished."
Manuel surveyed the desolation around him. On the whiteboard was scrawled the words ‘Maria is a witch!' The server had been pulled out of the 19in. rack and lay in bits on the floor. When love dies, Manuel mused, it turns to anger, a terrible anger that lashes out in all directions. Still…it didn't quite make sense.
"Well, we've got the rest of the weekend to clear up the server room, and put the database back together in time to begin trading on Monday."
"Can we start by cleaning the whiteboard?" muttered Maria, one of the developers, pointing at Raul's parting message.
"Raul is in serious trouble, don't worry about that, but without him we're going to have a struggle to put things back together in the time we have. Where is he?"
"I phoned his home but his mother says he's gone abroad. Don't worry, we can restore the database. We have the backups," said João, reassuringly, but his confidence didn't ring true.
Scene 2: Running to the wrong base
Two hours later, a new server sat in the rack, flashing red lights. It felt like progress but the developers looked wretched.
Nervous foot shuffling and coughing greeted Manuel's forced, optimistic "So…how's it going?"
"It's hard to know. We tried a restore but there seems to be something wrong with the latest full backup," sighed Maria "The restore failed with a pretty scary error message…"
"The restore failed with a pretty scary error message…"
Verify your backups automatically with theSQL DBA Bundle.
Manuel bowed his head. "We need a good DBA, in a hurry."
"With the problems facing us, I think we need the whole DBA Team" smirked João "You know…if you have a problem…if no one else can help…and if you can find them…maybe you can hire… "
Manuel waved his hand dismissively "The DBA Team are a myth…and anyway, I think we could do without adding silly disguises and exploding jeeps to our list of woes."
"No, no, they are real all right, but forced into hiding accused of a crime against database normalization they didn't commit!"
"Stop being a drama queen, João," interjected Maria, briskly. "A more sensible idea would be to get a good consultant like Robyn Page to help us sort this out. I happen to be in contact with her mentor, Phil Factor…"
"They're not real either!" Manuel blurted, in exasperation "Phil Factor is clearly a made-up name and Robyn Page…well, no-one that glamorous could really be in IT…"
"Just because of how she looks you assume she can't also be a talented DBA?" Maria fumed, glaring around the room menacingly. "No wonder half the human race avoids going into a profession stuffed with post-pubertal, unreconstructed misogynists with personal-hygiene problems!"
"Err quite. Yes, yes, slip of the tongue, I meant… oh never mind. I'll contact Phil right away and see if he can set up an urgent visit from Robyn…"
Scene 3: I spy
Bruno put down his wine glass and languidly answered his phone.
"Raul….how are our friends coping over at Acme?"
"Floundering like kippers, boss, as we predicted…"
Bruno laughed heartily. Hacking into the security IP camera that Raul himself installed in the server room was a masterstroke. Now they had a live video feed into their misery.
"…. but they are talking about getting in help from some SQL Server dame called Robyn Page…"
Bruno stopped laughing, and chewed his cigar menacingly. "Doesn't sound likely, Raul, but we ain't going to let it happen anyway."
With that Bruno leapt to his feet and made his way to his jeep, "Gabriel, keep an eye out for anyone approaching Acme's IT Building."
Scene 4: Baby sitting
Robyn picked up the phone, expertly burping the baby balanced on her knee.
"Uncle Phil, good to hear from you. A job, you say? Wow, yes, I'd love to be back on the TV!" cried Robyn, excited.
"Oh, you mean a DBA job…you know, Phil, those were good times we had running that server room, but years of stuffing baby food into gaping mouths takes its toll. I've forgotten all that I once knew about databases".
"You'll soon brush up. Remember what we used to say…'If it's not in Stanek's Pocket Guide, it ain't worth knowing'?" said Phil, encouragingly. "And I tell you what…we'll wire you up and I'll give you backup from a café over the road….I'll be a reassuring voice in your ear when the going gets rough."
Robyn sighed…."How much will they pay? Is that per hour? Oh, per day…"
There was a crash in the other room followed by a wail and then heated screaming. Robyn sighed again, more wearily, "I've got to go, Phil. I fear my daughter's 'My Little Pony' dressage unit has collapsed. Look….I'll do the job, but only if Steve and Grant are there with me, plus you over the wire."
"Deal. Looks like it's another job for the DBA Team…."
"Oh, and you'll also need to arrange a baby sitter…" said Robyn, before hanging up.
Scene 5: Reluctant reunion
"Grant, have I got a job for you! DBA gone crazy, server in bits…"
"Man, what sort of DBA destroys his own server…it makes me…SO…ANGRY…" Grant clutched his temples.
"And this time someone wants to actually pay us!" Phil interrupted hurriedly, "Well, actually they want to pay Robyn."
"What for? Do they want an actress or a professional DBA? What's wrong with my looks?"
"You'll get a cut. These guys don't know about the DBA Team. Robyn needs help. She's OK with the basics but a bit rusty with the
DBCC commands, if you get my drift. You and Steve will be there to provide expertise when she gets stuck. I'll be in contact with Robyn over a wire. Easy."
"So you want me to stand next to her while she sorts the problem out? I'm a DBA, not a ventriloquist, and she's no dummy."
"Did I mention that it sounds like the crazy DBA messed with the latest backups…"
"It sounds like the crazy DBA messed with the latest backups"
Recover from onsite disaster with SQL Backup.
There was an awful silence.
"His own backups….!" Grant clutched his forehead. "Don't make me angry! Aw hell! It's clobbering time!!"
"Steve? How's life on the ranch?
"Phil, pardner. A call from you means trouble, so quit the small talk and spell it out!"
"Steve, you've gotta believe this, someone wants to actually pay us for our database skills."
Steve Jones dropped the hayfork in surprise. The horse rolled its eyes in sudden panic and trotted a couple of times around the field, neighing. "That'll be the day."
"I'm exaggerating slightly. They want to pay Robyn but she won't go unless you and Grant are there as backup."
"There is no such a thing as backup, only a restore."
"She just needs someone to help her when the purple wobblies start to wobble . Will you do it? A simple trip, it'll be over in a couple of hours."
Steve chewed on a straw for what seemed like forever before saying, "Now you understand. Anything goes wrong, anything at all…your fault, my fault, nobody's fault…it don't matter…I'm gonna sing you extracts from my SSC Christmas editorials for all eternity."
Scene 6: If in doubt, Kanban
Robyn and Phil gazed nervously out of their taxi as they pulled up outside the Acme building. Robyn had her phone to her ear and was giving elaborate instructions to her baby sitter on warming the milk. With a sigh, she snapped the phone shut. "I don't think I can remember any SQL Server at all. Give me a test question, Phil."
'How do you restore a database?'
"Well, a good Napoleon Brandy, of course. Certainly not rum and pineapple juice…Seriously, Uncle Phil, I don't think I know my SQL from my Celko right now."
"How do you restore a database?"
Handle all your backups and restores in one place with the SQL DBA Bundle.
Phil laughed nervously. "Grant will be here soon. And don't forget, I'll be in the Café on the other side of the square at the end of the phone."
Robyn sighed. "If things go wrong, I suppose we can always get them to do the Kanban."
"Well, the developers might be game but I don't think Grant will put on stockings, link hands, and kick his legs in the air for anybody, plus the kilt…."
Robyn managed a smile, "Don't fall asleep over your wine. I'm going to need your help in there." With that, she got out of the taxi, and walked up the steps into Acme's IT office.
Across the square was the glint of a pair of binoculars. Gabriel stared intently at the distant figure and swore.
"Hey boss. A classy dame just arrived in a taxi. I'm all tooled up and going in."
Scene 7: More to a database than meets the eye
Maria ushered Robyn into the server room, glowing with smug pride. They surveyed the dead server. By rights, there should have been a chalk-mark around it.
"OK", shouted Robyn, decisively, clapping her hands and bringing the group to attention. "I assume you've got a cold standby and at least attempted a bare metal restore of the database and environment?"
"What I mean is, Raul should have set up a spare server with SQL Server installed and configured just like production, where you can attempt a restore."
"Ah, yes," cried João, relieved, "there is a problem with the latest full backup but we got the one before that from the archive. It's out of date, of course, but we wanted to prove we could get everything running on the…err…cold standby"
"And it worked….?"
"Well, put it this way, if anyone ever starts an archive of interesting but blood-curdling error messages, they'd do well to give us a call. The restore worked and we have access to the database but the application won't run at all."
"Did you restore the entire server environment before attempting the database restore? This will also bring back any other applications and data which may have resided on the server at the time of failure."
"You mean that there should be a Windows backup of the server as well as a SQL Server backup?"
Robyn sighed, "Yes, there are often quite a few things stored on the SQL Server instance, or the server itself, that are required for a database to work correctly in the production environment…I assume you've got a backup of the system databases?"
There was a faint bleat from the developers. Robyn gulped. No Windows backup, no system database backups, broken full database backup…It was going to be a long day.
At that moment the door burst open. There stood Gabriel with a Kalashnikov under his arm, looking as if he meant business. "Don't move!" he shouted, as he stormed in, slamming the door behind him. "You really cooked your duck this time," he snarled, leaning menacingly against the door. "Hands in the air where I can see them!"
João's hands shot into the air - his caiprinha went flying.
Gabriel glanced scornfully at the lime wedges rolling under the server, before turning to Robyn. "OK, Miss Robyn, step right forward slowly, Mr Bruno wants …."
Crash. "Hey guys, sorry I'm late. Just show me the DBA who…" Grant stormed in, waving balled fists. He stopped dead in his track as Gabriel slumped dramatically to the floor, from behind the door, and his gun skittered across the floor.
"Sorry, didn't see him there…a Kalashnikov?! Oh boy, your users sure take database down-time seriously."
"Just in time, Grant!" cried Robyn, as the developers immobilized Gabriel with the vogon nerve-grip, and dragged him downstairs, "the most recent production database backup won't restore…"
"OS backup? System database backups? I see….well someone at least show me the latest production backup," cried Grant, throwing his hand up in despair.
He attempted a restore, confronted with a screen of red. He tried again, this time just
RESTORE HEADERONLY…."Why do you suppose the Backup Description field says "Maria is a witch?"
"You think Raul tampered with the database full backup??…Of all the lowdown, dirty…"
"Maria," Grant cut her off, "call your offsite backup people immediately and tell them we need the latest backups here within the hour."
"We need the latest backups here within the hour!"
Recover data whenever you need with cloud storage from the SQL DBA Bundle.
"I already did. They said we only pay for 48 hours delivery and they couldn't possibly do it faster than 36."
"OK then, well…we'll just have to go down there and get them ourselves".
Grant and Maria hurried out.
"In the meantime," said Robyn, addressing the devs, "we've got to be able to find backups of master and msdb, somewhere, so we can restore the agent tasks and alerts. Really? No?"
"I think we have the scheduled jobs in source control though…" offered João.
Robyn took a deep breath. "Well, that's something at least. Let's get scripting…."
"Hey Bruno, more bad news. They are going after the offsite backup with a mean looking guy called Grant Fritchey in tow."
Bruno smiled and stubbed out his cigar. "Well let's hope they don't run into any trouble along the way…"
Scene 8: Break out the backups
Grant and Maria sped out to the backup facility in his beat-up pickup truck, crossing the old river valley. Maria looked nervous.
"You know, Grant, there's a chance they may not be so keen to hand over those backup tapes."
"Why not? OK, so you only paid the 48 hour turnaround and we need them a bit quicker. Big deal."
"Well, it's more that we may not have paid at all for a few months…"
Grant looked at her slowly. "We'll find a way. Nobody keeps this DBA from his backups."
"But this is a high security facility. There will be guards, we'll need clearance. Even if we somehow get past them, we'll need authentication, pass codes to get in the building…"
Maria fell silent as Grant pulled up outside and parked in a slot marked "Reserved for C.S.O." They got out.
"OK, so no guards at the gate for some reason, but there is no way we're just going to walk into here." Maria fell silent again, as they noticed a rusty old metal door standing ajar. A little further down were two guards, backs to them, chatting over a cigarette. Two minutes of rummaging and they had their backups. Grant peered around the door.
"Put it this way…"
Scene 9: "Ruuuuuunnnnnn…!!!!"
The pickup tore down the highway, tires smoking
In a side-turning, Bruno stood up in his jeep, looking through binoculars. "That's them. Pedal to the metal and after them." He grabbed his machine gun. Their jeep shot off in a cloud of dust.
Maria glanced back, frantic, "We're being pursued. If we can just get back across the valley bridge, I know how we can lose them…but… Hell, they're gaining on us"
They swerved onto the bridge, the jeep right on their tail.
"I don't think we're going to make it," screamed Maria.
They were almost across the bridge when out of nowhere a figure in a Stetson and a really terrible shirt loomed right in front of them. He was riding a handsome steed…
"It's Steve!" cried Grant, the pickup swerving and missing them by inches. The horse reared, the jeep careered wildly to the right, smashed through bridge barrier, flew briefly through the air before crashing into the valley slope.
Staggering from the sedan, wincing at each loud bang, they saw the jeep begin its rolling, crashing descent.
"Howdy, pardners!" Steve lifted his hat "I rode in up the valley this morning."
"Steve…Why did you do that!?"
"What? I had no choice…the Prius is at the garage…"
A massive explosion sent everyone to their knees. Peering down from the bridge, they saw the mangled, smoking carcass of the jeep on the valley floor, and two distant figures crawling from the wreckage, disoriented and dishevelled but otherwise completely unharmed.
Scene 10: We have a database, people!
"Grant, you have the backups!" cried Robyn as they entered.
Grant nodded with a steely determination in his eyes, and immediately got to work transferring them for restore to the standby server.
"Howdy, Steve. What took you so long? We've already managed to come up with a complete build script for the agent jobs and alerts."
"Yep, Grant and Maria brought me up to speed. We've still got a lot to think about while Grant test restores the latest backups…security credentials, the possibility of orphaned users and broken ownership chains, connections, server configuration, database properties…"
Robyn intervened. "Whoa there cowboy, let's get all this down" She started to write on the whiteboard, before checking herself and grabbing a pack of post-its, building a KanBan board to begin to layout the tasks that need to be completed.
"Let's start at the beginning with Security." She wrote the heading on the board. We need to recreate any Windows logins that need to exist on the server. She slapped the Windows logins post-it on the 'Tasks' area.
"How do we know which ones we need?" asked João.
Steve piped up, "A database user maps to a server login, which is stored in 'master'. Once Grant restores the database, we use the
sys.server_principals catalogs to check them. The SID maps from the user to the login. It looks as if your application only uses Windows logins, so all we have to do is create those logins on the server."
"Also, check for orphaned users," continued Steve. Robyn duly slapped up the post-it. "The SID and password for every database user must match its associated Windows login. If you find any, synchronize them with
ALTER USER…WITH LOGIN."
Maria suddenly rushed up to Steve. "I can hear a strange echoey voice in the server room. It sounds a bit like Raoul, the DBA, but it sounds as if he's inside a biscuit tin."
"Hmm. Bandits, security guards, now ghosts. This ain't no ordinary database consultancy job."
In the server room, a voice was issuing from a security camera at the top of the server rack. "Hiss….crackle….Hello boss? The reception is terrible…Eh? What are you doing down there!?
Maria ripped the camera off its mounting. "Silly guy didn't switch the microphone off."
Back in the main office, Robyn was still in full swing "Create all the SQL Server logins on the server that you know about as well, just in case they're used by a process." Up went the post-it.
Steve broke in again. "We also need to check database ownership to make sure that the database owner hasn't been orphaned and that it is related to a login on the new server. You'd need to use
ALTER AUTHORIZATION ON DATABASE:: to fix this if necessary."
"OK, what next"
"Connection strings," suggested Tom, one of the quieter devs. "We need to check the application build to make sure that every connection between the application and the database addresses the new server, which will probably mean wading through lots of connection strings and
ODBC configurations within the application."
"Good", cried Robyn, slapping up the note and sensing the developers were finally warming to the task. "Also, test the connection to make sure that it isn't being prevented by firewalls or DNS entries." Slap.
Next, up went server configuration settings. "By default" said Steve "when you install a SQL Server instance, some features that the database might need are disabled, so we need to check that."
"Hey, I seem to recall Raoul scripted out the production settings with a PowerShell script. I think we've got those in source control, and probably the database property settings too."
"I think we've got those in source control"
Deploy directly from source control or backups, with the SQL DBA Bundle.
In a similar fashion, up went notes for database mail, collation settings, service broker, maintenance plan checks, and more, until the board look liked a healthy tree decked in fluorescent colors.
"Excellent!" shouted Grant, punching the air "We've restored the latest full backup and the full log chain. The disk housing the log file avoided the Piña Colada so we've lost almost no data. We have a database, people!"
"OK, we're all set. There are five of you," said Robyn, "so let's allocate the top five tasks, and when you're finished, just pick the next unallocated, and put the post-it for the job you've done in 'Done'.
Soon, a flurry of hands were pulling off post-its and, after a while, slapping them in the 'Done' area.
Steve looked on in amazement. When all the work was done, Robyn clapped her hands like a schoolteacher and said, "Right, let's run the new version of the application and make a note of every error message there is. Then just work through them."
There was a hush as they went through the build process for the application, and restarted the server. It seemed like an hour but wasn't. There were no errors.
Grant, Robyn, Steve, Maria, and all the developers linked hands and began kicking their legs in the air, in something approaching synchronization.
Scene 11: Not just a pretty face
Phil nursed his class of wine. Steve, Grant, and Robyn strolled over. "Phil, that was great. You overdid it a bit though, didn't you? I thought that the developers would suspect that you were prompting Robyn."
"Overdo it? I dialled Robyn's phone and all I got was some silver-tongued dame telling me I was first in the meeting and then she kept playing calming music at me."
"Robyn? Was that Lady Gaga or Phil you were listening to?"
"What can I say? One day I had this Gonzo headache, and before it was over I could speak and read SQL Server."
You'll need fewer heroics and less luck with the SQL DBA Bundle
The SQL DBA Bundle includes SQL Backup Pro, the world's most widely-used third party tool for SQL Server backups.
SQL Backup Pro 7 lets you schedule backups and restores and take compressed, encrypted backups. It can also test your backup files after they're created by including
Try it today as part of the SQL DBA Bundle, which contains SQL Backup Pro and six other tools that help you keep your data safe:
- Create up to 95% compressed backups and schedule regular restores, including full DBCC integrity checks.
- Protect your data from unauthorized access with 256-bit AES encryption.
- Monitor your servers in real time so you're always the first to know about problems.
- Review full details of schema and data changes going into your servers, including SQL CREATE scripts with highlighted differences, so you can deploy updates with zero errors.
- Save time writing SQL with code completion, code formatting, and customizable SQL snippets.
SQL Server Backup Pack
- SQL Server Backup & Restore eBook by Shawn McGehee
- Robyn Page's SQL Server Backup Crib Sheet
- Answers to 14 backup questions people are too shy to ask, from Grant Fritchey
Download all three resources for free in our SQL Server Backup Pack.
Plus, receive emails about new episodes of the DBA Team and other helpful DBA updates.
You're now signed up to receive new episodes from the DBA Team.