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 mistakes 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 mistakes 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 s...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
chedule 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 SearchSQLServer.com Backup and Recovery expert, welcomes your questions.
More information from SearchSQLServer.com
Tip: Automating Server restores
Ask the Experts: Ask Greg Robidoux for answers to your backup and restore questions
Topic: Research SQL Server backup and recovery topics
