Manage Learn to apply best practices and optimize your operations.

Worst practices for SQL Server backup and recovery

In the event of a SQL Server failure, wouldn't it feel good knowing you have a well-tested and reliable backup and restore process to protect your data? The success of your efforts depends more on seemingly small oversights or mistakes you make than on the best practices you follow. In this tip, Edgewood Solutions' Greg Robidoux identifies backup and recovery worst practices.

The following is the first part of a two-part series on SQL Server backup and recovery worst practices. If you have any backup and recovery worst practices you would like to share, please let us know.

Having a reliable backup and recovery process is essential to protecting your SQL Server data when things go bad. Tips often explain the key elements and best practices of setting up a backup solution, but the worst practices often make a greater impact on your environment. The following tip highlights some of the most common worst practices that I encounter when evaluating a company's backup and recovery practices. Take a look at what you must not do and – if you are doing them -- how to set things right.

Fail to test backups

Observation: What good are backups if you don't test them? Many people create database backups but never test them to make sure the restore will work. Usually they discover this problem during a crunch to get a database restored after a failure. Believe me, this is not the time to keep your fingers crossed, hoping your backups are good and your restore process works.

Recommendation: You should periodically test your full, differential and transaction log backups on a standby server or a development server. You don't need to test every single backup that you create, but you should go through the testing process for a full recovery just so you know the steps and how the process works. If your backup files are intact and the restore is successful, you are pretty much guaranteed that your backup files will be good in the future. However, things may change on your network or server that are out of your control, so it is a good idea to run your tests on a set schedule (i.e., once a month) to ensure nothing has changed that may negatively impact your restore process.

Fail to check if backups were successful

Observation: Using SQL Server Agent or some other scheduling tool to schedule your backups is a great way to make sure they run at a set time. Along with setting the schedule, you need to check that the jobs completed successfully. I often see installations where a backup was scheduled and then forgotten about. A cursory review of the system shows that the backup jobs have been failing and, therefore, no good backups exist or have existed for quite some time.

Recommendation: The simple thing is to check your scheduled jobs every day to make sure they completed successfully. While you're doing so, look at how long the job took to complete and make sure everything is running according to that baseline. A better approach is to use Operators and have the job notify you via e-mail or pages if there is a failure. A future tip will explain how to set this up in SQL Server.

Write backups to the same physical disks as data files

Observation: The purpose of creating a database backup is to ensure you have a backup copy in case there is a system failure, which could just be a drive failure. If your database files are on drive A and you also write your backup files to drive A, a drive failure will cause you to lose both your data file and backup file.

Recommendation: Create your backup files on a separate physical disk to alleviate any chance of losing both your data file and backup file. Even better would be to create your backup files on a different server in case there was a complete server failure. The only drawback to creating your backups across the network is the unknown of other network traffic, as well as the potential for a network connection loss.

Write backups directly to tape

Observation: There are several tape backup products on the market that allow you to write your SQL Server backups directly to tape. This is great if you don't have the disk space to support having a backup file on disk -- but is this really the best option when you need to do a restore?

Recommendation:The better approach is to write your backups to disk first. This is usually faster unless you have a high-end tape backup system. You should create backups on disk instead of tape because you will be able to immediately identify where to find the backup file on disk when you need it. Having the file on disk will probably make the restore process faster as well because disk-to-disk operations are faster then tape-to-disk. Tape backups are still important and should be used for long-time data archiving. But 99% of the time you will want to restore the most recent backup from a point that is close at hand.

Fail to run the verify option

Observation: One of the SQL Server restore commands is RESTORE VERIFYONLY. This command checks backup integrity and lets you know that the restore process can successfully read the file. In most cases, this step is overlooked because it often takes just as long to run this step as it does to run the entire backup.

Recommendation: Although it does take extra resources to run the verify option, I would rather know that the backup file created will be usable for a restore when needed. If you are concerned about running an extra process on your production server, don't worry because this can be run on any SQL Server, not just the server that created the backup. It makes more sense to run this command and know the outcome than to just "wait and see" when you do a restore.

These are just a few of the common worst practices for backup and recovery. Look for additional worst practices in a future tip. If you have any backup and recovery worst practices you would like to share, please let us know.

About the author

Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who serves as the Backup and Recovery expert, welcomes your questions.

Dig Deeper on Database management system (DBMS) architecture, design and strategy

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.