Inconsistent backup plans
While it is hard enough to manage SQL Server, you find that having inconsistent backup plans from server to server will make your life even harder.
Instead, try to classify your databases into a few groups and then apply the same backup plan across the board to these different groups of databases.
Not including new databases in backup plan
It is not uncommon to hard code which databases to back up when you set up backup plans. You should make sure you make this flexible enough to add new databases. But also monitor your backups to make sure you are not missing any new databases.
Non-centralized backup processing
If you have a lot of servers to manage, look at setting up a centralized backup process, so you can manage everything from one place. This can be done by creating your own processing or using third -party tools that allow you to do this right out of the box.
Taking the database administrator out of backup processing
While creating backups may seem like a routine thing, there are still lots of mistakes made every day.
For example, when recovering databases in SQL Server, there is a lot more to it than just doing a file restore. The backup plan you have for your file servers may not work for your database servers, so make sure you do not take the database administrator out of the backup and restore process.
Inconsistent naming conventions
You should make sure you use a standard naming convention when creating your backups. SQL Server, by default, uses bak and trn. But you could also use dif for differentials and flg for filegroups.
You should also include the server name, instance name, the database name, and the date the backup was created. This allows you to just look at the file name and know what is in the file.
Using multiple applications or schedules to do backups
Another problem is when there are multiple backup applications backing up the same database. So maybe some backups are done natively and other backups are done using third-party tools. If you are mixing the tool along with the scheduling, this make is difficult to do restores. This could become a real problem, so be sure to keep things consistent and do not backup the same database using different tools and or schedules.
Writing backups to inconsistent locations
Another issue is writing backups to different locations. For example, full backups are written on the E drive, log backups are written on the F drive. This could get confusing if the files are not kept together. Also, for someone who is not familiar with the server, this makes it even more confusing. So be sure to keep a complete backup set of files together.
Not removing old files and data
In most backup processing, you can delete older backups to manage disk space. Also, make sure you clean up older log files and purge some of the old history data from the backup and restore system tables.
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..