It’s October. That means fall, colorful leaves on the trees, soups simmering on the stove, and…horror movies. I love a good horror flick.
It’s also another month of T-SQL Tuesday, edition #35. Our host, Nick Haslam, asks, “What is your most horrifying discovery from your work with SQL Server?”
Once Upon a Time
Long, long ago, in a land far, far away, I was the very junior DBA at a small company. We had one main production server that hosted the databases for our financial and sales applications. I took great care of this server. I made sure backups ran every day. I tested restores (to a separate server, for reporting) regularly. DBCC CHECKDB, index maintenance, and statistics maintenance were run regularly. I had alerts set up in case jobs failed, and I could use Activity Monitor and some of my scripts to check for problems when I got the inevitable “the system is slow” calls.
Then, one dark and stormy night, a disaster happened.
The drive the data files were on failed.
I remained as calm as I could during My First DBA Disaster. Because we ran restores regularly, and occasionally they failed and I had to manually do them, I was able to walk through the steps quickly and without panic.
But things were not as they seemed. There was trouble. Users couldn’t connect. Applications couldn’t connect. Regularly scheduled jobs were running. Things didn’t seem…right. It was determined that while we were taking backups of the user databases, we weren’t backing up the system databases.
We didn’t have backups of master, model, or msdb. System configuration settings were lots. SQL Server Agent jobs were lost. It was a mess. We lost a day of business while re-configuring the server and database-level settings. We lost another day of backups and maintenance because Agent jobs had to be recreated and tested. We lost two days of work on projects and reports because we were busy mopping up the mess from that disaster.
Frightened Into Backups
I learned the hard way that you need to back up both system and user databases. That was a change I implemented immediately. Every DBA should regularly review the backups on their servers to ensure that all the databases needed for recovery are backed up!
Do you want more guidance on which databases should be backed up, and what recovery model they should use? Microsoft offers guidelines!
OK, you can turn the lights on now!
Jes Borland is a Premier Field Engineer - SQL Server for Microsoft, where she is a trusted technical adviser, deep technical support, and teacher for her customers. Her experience as a DBA, consultant, and 5-time Data Platform MVP allow her to help the business and the IT teams reach their goals. She has worked with SQL Server as a developer, report writer, DBA, and consultant. Her favorite topics are administration, automation, and performance. She frequently presents at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef.
