Improve your testing and scheduling for SQL Server backups

Testing

Not using restore verify option

The last thing you want is to discover that your backup is not good when trying to restore it. A restore verify does at least give you some peace of mind that SQL Server could read the contents of the backup without issue, although doing it does not guarantee that the restore will be successful.

To add this step is easy when you use maintenance plans. But when you create your own scripts, you should also include this step in the process as well. You should monitor the output from this command to ensure the restore verify did not have any issues.

If it had issues, you can always create another backup.

Not testing backups

Another item to consider is testing the entire restore process.

You should randomly and periodically do complete restores of your databases to ensure there are no issues. This also gives you the ability to rehearse your entire recovery process, so you know what works and what does not during a failure.

Scheduling

Wrong backup schedule

The wrong backup schedule is another common problem. In a lot of cases where a full backup is done once a week, differentials are then done every other day of the week. This may be your only option if you do not have space. But space should not be an issue anymore. Another common scenario is to have the database in full recovery, doing full backups every day and then truncating the transaction log. If you are going to throw the data away, you might as well just use the Simple recovery model.

You should have a mix of full, differential, and transaction log backups and try to avoid doing high input/output activities, such as full backups during peak usage times. Another issue is that full backups are run multiple times a day. While there may be a need for this, keep in mind that you could also use differential backups with the full backups to minimize the impact.

Read the whitepaper “Top SQL Server backup mistakes (and how to avoid them) by Greg Robidoux from MSSQLTips to learn more about improving SQL Server backups..

[Download PDF]

Anonymous