In this article we explore situations where Backups might fail when you are dealing with a large SQL database.
Backing up data in SQL Server is a process that needs to be done with lot of caution, there are lot of little details that need to be taken care of when executing a backup. Unintentionally you might commit blunder mistakes, leading to a failed backup, and in-case of large backups, things only can get worse. Given below are a few of the most common situations classifying as backup failures, explained with their causes and consequences.
- Missing Full Backups – While you might believe that you have successfully backed up data, and it is available online, you might be in for a shock. SQL will make no attempts to check if there is any full backup available online, if your maintenance plan is set to automatically deleting backups after a certain time period, your full backups might be getting deleted, without you having the slightest knowledge about it. And having differential backups without the full backups is no good.
- Multiple Users Leading to Missing Backups – Sometimes you might end-up with missing full backups because someone else refreshed the development server by taking a full backup on production. The differential backups are always available along with the full backup files, so to access even the differential backup, you should know the exact location of these files.
- Large Differential Backups – By regularly rebuilding indexes, you will make your differential backups as heavy as full backups. This will not only make them occupy more space but also involve much more time for restoring them, thus making your backup strategy a complete failure. While backing up, it is important to always ensure that the backups occupy less space and restore quickly, the complete opposite of this will happen if you keep on maintaining indexes. Ideal time for maintaining indexes is just after you have completed your full backup.
- Offsite Backup Complexity – If you need to take your backups offsite, moving the differential backups to tape will not be sufficient. You will have to move the full backup along with all the differential backups, to help in the process of restoration. The full backup and the last differential backup need to be together, only the differential backup on tape will not help in SQL Server db recovery if disaster strikes. The process of recovery can be made complex by other processes like mounting, inserting, reading etc. Differential backups can certainly save lot of time and provide with necessary backups to ensure restoration, but they also need additional management discipline, which if not provided can turn them into a greater hassle than not having any backups at all.
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair accdb data and sql recovery software products. For more information visit www.datanumen.com