Author: way0utwest

I saw a post recently from my fellow Red Gate-er, Brad McGeHee, on backups. Brad’s got a ton of experience and he puts out some great posts that help many DBAs do a better job each and every day. I highly recommend you follow Brad’s blog if you’re new to the field.

I think that this is great advice, but I wanted to expand on it a bit since I’ve had slightly different experiences than Brad, and I’ve had a system that worked well for me.

My experience has primarily been with smaller databases. They’ve ranged from MBs to a few hundred GBs, nothing TB sized in my production experience, which for the most part ended in 2003. Since then I’ve worked only for some small companies as a consultant and for SQLServerCentral.

If time and hardware allowed it, I have always run full backups every night. That provides me protection for data that typically changed substantially every day, and was critical for the business. A full backup gave me a relatively quick way to recover the database in the event of an issue.

I have always run log backups as well. Only in very rare circumstances, < 1% of the time, have I managed databases in simple mode. The time between log backups has varied depending on the recovery interval I’ve needed, but typically log backups are run as rarely as every hour and as often as every couple minutes.

That’s Brad’s advice, and it’s worked well. The place where we disagree is with differential backups. I have found them to be a handy tool that speeds recovery for me. If you’ve ever had to recover a lot of logs, like the number from midnight until 6pm the next day, you realize it’s a lot of work. Even if you have scripted the recovery, it’s nerve wracking to try and restore all those logs in order and ensure that they get your system back. And it’s time consuming to try and debug any issues that may crop up.

After one particularly harrowing RAID controller failure, I decided that I’d use differentials as a way to speed recovery. With a differential every two to four hours, you can cut the number of logs you need to restore substantially. You also reduce your risk of a possible problem with some of your files by having fewer files to depend on at one point in time.

Saving Dollars

I found another reason to use differentials at my last job before SQLServerCentral. I worked for a large company with hundreds of SQL Servers. We had a large TSM backup device that handled file servers, mail servers, and SQL Servers. At that time, the TSM was configured to only back up changed data (incremental in backup terms) and we were backing up over a TB of data every day. In 2002, that was a lot of data and it cost us literally hundreds of dollars a day in tape costs.

At the time I was working with DBAssociates, the inventors of Litespeed, trying to build a case to use the product on our SQL Servers. It seemed like it would be a good use investment, and I had to write an ROI paper to present to my boss. As I was working on it, it occurred to me that if I moved away from nightly full backups to a weekly full with differentials I might save even more space. I tested the backups, extrapolated out the savings in backup space, and included the data in my report.

At the time we bought nearly $60,000 of Litespeed licenses and paid for them in a little over a year with tape savings. The savings were partially from the compression of data, but also from the move to differential backups instead of full backups each night.

Have a Good Process

Part of the reason that I’ve been able to have success with differentials is that I’ve always ensured we had a good process for not only keeping a full backup on disk, but also ensuring that a good copy was moved offsite each day. This protection from an issue in the office, which I have dealt with, helps to make a differential backup a tool worth using.

Evolving your backup process from a simple “copy to disk or tape” into something that include offsite recovery, differential backups, and good procedures that non-DBAs can follow will save your company money, and at the same time reduce your risk of data loss.

You can even further evolve things with other SQL Server technologies like log shipping, replication, and database mirroring.

I’ve heard imitation is a form of flattery, and I guess that’s true. Today I got an up close and personal look.

I saw this note at Database Journal that they were starting a new daily newsletter with daily news: Database Daily

Some of you might remember that Database Weekly, the other site that I run (with help from Phil Factor, Tony Davis, and a few others at Red Gate software), used to be known as Database Daily. The name changed when we realized that we would likely never put it out daily, but rather stick with weekly. At least someone at Red Gate realized that. I was happy with Database Daily, and often updated the website on a daily basis with links.

When we came up with the idea, Andy, Brian, and I debated names for a few days, and liked Database Daily, so we grabbed it. Our plan had been to start with a weekly newsletter and as we found a decent amount of news, we’d add in more newsletters.

However the load of adding stuff daily was too hard. I managed it by adding links twice a week (Tue and Fri) and that’s still how I do things, though occasionally I see something on other days and just add it.

I’m not sure if we still have the domain, but if we do, I think it’s worth hanging on to. Especially now that someone else things it’s a good idea.

The other day I was trying to help someone that had removed the BUILTIN/Administrators logins from their instance without first setting up another user/group as sysadmin. Actually I wasn’t helping. I was telling them that finding the media would be a good move to prepare for the reinstallation.

Then someone posted a note that if you start SQL Server in single user mode, then you can connect with the administrator account and get sysadmin access. It’s true and there’s a BOL entry about this: Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out

I was surprised that this was the case. If you get locked out on your Windows machine, you’re basically done. There are a few password cracker programs, but the reality should be that if you don’t have the administrator password, and you don’t have any administrator groups define, you should not get in. Sucks if it happens, but that’s strong security.

Why is it different with SQL Server? If someone can log on as “administrator” and start SQL Server in single user mode, then they have access to all your data. I know you should trust your admins, but typically we have separation of duties, and the Exchange administrator should not necessarily be poking around in SQL Server.

On top of that, “administrator” is a generic login. Your auditing won’t tell you who connected.

I think this is a problem, and I actually need to write an editorial on this. It’s a hole that I think deserves to be removed.

The other night I was tired, a few long days, early mornings with the kids, and slightly wired, but needing to rest. My wife was alseep, so I blogged a bit, watched a little TV, and then turned out the light about 10:30.

I lay there, and for some reason, about 10:50 or so, I woke up. I was pretty sure I hadn’t scheduled a newsletter. It just occurred to me, so I grabbed the laptop next to the bed, fired it up in the dark, my eyes 2” from the keyboard trying to type my crazy 10+ letter password.

I checked, and sure enough it wasn’t scheduled. I did a quick schedule and test, and then went to bed. Strange that I’d catch it that late at night, but glad I did.

Some days it pays to have the laptop next to the bed. I might not have gotten up if I’d have to go downstairs to check that computer.