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!