Problem solve Get help with specific problems with your technologies, process and projects.

The keys to database backup protection for SQL Server

There is a lot more to database backup than simply saving your info to a disk. Learn the steps that must be taken after the backup process to ensure that you and your database are 100% protected.

So you've read all the articles and blog posts detailing the importance of database backups and set them up accordingly. You are now finished, right? Wrong.

Backing up your databases is only the first step in the process. The next step is to make sure those backups are protected. You also need to test the backups and to ensure that they can be used to restore your database. The only thing worse than not having a database backup when you need one is thinking you do when you actually don't.

Storing your backups

Probably the most common database backup technique involves backing up the database to a disk on the same server. This is fine, provided the disk is a separate physical RAID array from the one that your database sits on.

Since backups are used to recover from worst-case scenarios, they need to be protected from such disasters. After all, what good are backups that will just be lost when the server fails?

The two most common things to do with these backups are to either back them up to tape shortly after they are saved to disk or move them to another server for long-term storage. Either solution is acceptable since you are left with a secondary backup. This way if the server fails and takes your original backups with it, you can still restore the database.

Testing your backups

Once you have your backups saved to another location -- either tape or another server -- you then must ask yourself whether those backup can be restored. Ensuring that your backups are going to be useful is an important step in the backup process. Using the Verify Backup Integrity checkbox in the maintenance plan is not enough, because it simply makes sure that the header of the backup is correct without verifying the validity of the backup.

The only way to confirm that the backup is indeed valid is to restore it.

The only way to confirm that the backup is indeed valid is to restore it. Now this doesn't mean that you should take your database backup and restore it over your production server. It just means that you need to restore the backup somewhere to ensure that the full backup and all the log backups are valid.

Doing this properly requires having an available server that you can restore the last full backup and all the log files to. A virtual machine would be perfect for this as the memory and CPU requirements are fairly low. If you have a problem restoring the system, first identify and correct the issue and then try again with the next day's full backup.

Securing your backups

Now that you've made sure that your backups won't be lost if the server fails, you need to see to it that no one else can take your backups and restore them to get your company's data.

The methods for this can vary depending on which technique is used to store your backups.

When storing backups on a disk, setting NTFS permissions on the folder and using the Windows Firewall or a hardware-based alternative to prevent unauthorized access may suffice. On the other hand, if you are storing backups on tape to ship off to a storage facility, or if you are using disk-based storage in order to copy them off-network, then you made need to find a more complex alternative.

Most tape backup solutions include a way to encrypt the backup when it goes to tape for this exact purpose. Check with your systems administrator to find out what options your tape backup system supports.

More advice for SQL Server backup and recovery

Choosing a disaster recovery solution

SQL Server 2008 backup compression pros and cons

SQL Server backups using SAN database snapshots

If you are backing up to disk and shipping the backups offsite over the network or your tape backup system doesn't support encryption, then you'll need to look into an encryption system outside of the backup solution. If you are running SQL Server 2008, you can use the Full Database Encryption option that encrypts the data in the database, so that it's already encrypted in the backup.

You can also look at third-party tools to encrypt the backup. Tools such as LightSpeed for SQL Server from Quest Software, SQL Safe Backup from Idera and SQL Backup from RedGate all support backup encryption while it is being taken, so only authorized users can restore the backups.

Once you have started encrypting your backups you will need to change your test policies again. You need to be sure that you can restore your backups to another server without using anything that is stored on your production server. This means keeping the encryption keys stored in a secure location where only authorized personnel can access them.

Testing automation

Once you have planned out the testing – which should be performed on a weekly or monthly basis -- you can work towards automating it. Most enterprise backup solutions have a way to automate restores for this exact purpose. This automation will also test your backups by restoring the files and/or databases from the backups on a regular schedule. Once the automated restore is complete, you should verify its success by having a status report emailed to you when it's finished. You can then keep this email as proof of your restore capabilities for your auditors.


Denny Cherry has over a decade of experience managing SQL Server, including's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP.

Dig Deeper on SQL Server Security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.