Not having a good recovery process
One big part of your backup process needs to include the recovery procedures.
You should have a rehearsed recovery plan, so when you need to recover any of your databases, you know the exact steps to follow. Also, keep in mind that there are many other steps to this than just the database backups, so be sure to put together a plan.
Restoring from tape
You should keep the latest full set of backups on disk to ensure the fastest recovery of your databases. Disk restores in most cases are always going to be faster than going to tape.
As a general rule, use disks for recovery and use tapes for long term storage.
Not having the most recent backups on disk
Another issue occurs when the most recent backups are not available on disk and therefore you need to search for the backups on tape.
You should keep a full set of backups, which includes the full, differential, and transaction log backups on disk. Also, it would be a good idea to keep two sets on the disk if possible. Anything beyond that perhaps makes little sense to keep on disk because you would want to restore the most recent data that you can.
Not having a priority order for recovery
We mentioned having a rehearsed plan for recovering your databases. In addition, set a priority order for the restore process.
A priority order allows you to tackle the most critical databases first. Also, if there are databases that depend on other databases, they can be done in an order that allows the applications to function. It is impossible to do everything at once, so think through the order that you will approach things during multiple failures.
Not knowing how to recover for applications that use multiple databases
This may not be a common scenario for everyone. But there are many of you that have applications that rely on over one database to function. Therefore, you need to think through the recovery process for this type of recovery.
Do all databases need to be recovered to the same point in time? If you have databases that are replicated, how does the recovery process impact replication? Also, what special steps do you need to take to restore the databases to get the applications working again if you are using third-party applications that use SQL Server? You should make sure you think about the complete recovery and not just one database.
Not backing up the tail of the transaction log
One last thing to consider is backing up the tail of the transaction log. This allows you to get any additional transactions that have not been backed up yet, so you can restore as much data as possible with minimal data loss.
This is something you should make sure you have the knowhow to do in case there is a failure. It is just like taking a transaction log backup. This may not be something you can always do. But you should know the steps.
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..